1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55class Expression(metaclass=_Expression):
  56    """
  57    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  58    context, such as its child expressions, their names (arg keys), and whether a given child expression
  59    is optional or not.
  60
  61    Attributes:
  62        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  63            and representing expressions as strings.
  64        arg_types: determines what arguments (child nodes) are supported by an expression. It
  65            maps arg keys to booleans that indicate whether the corresponding args are optional.
  66        parent: a reference to the parent expression (or None, in case of root expressions).
  67        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  68            uses to refer to it.
  69        comments: a list of comments that are associated with a given expression. This is used in
  70            order to preserve comments when transpiling SQL code.
  71        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  72            optimizer, in order to enable some transformations that require type information.
  73        meta: a dictionary that can be used to store useful metadata for a given expression.
  74
  75    Example:
  76        >>> class Foo(Expression):
  77        ...     arg_types = {"this": True, "expression": False}
  78
  79        The above definition informs us that Foo is an Expression that requires an argument called
  80        "this" and may also optionally receive an argument called "expression".
  81
  82    Args:
  83        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  84    """
  85
  86    key = "expression"
  87    arg_types = {"this": True}
  88    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  89
  90    def __init__(self, **args: t.Any):
  91        self.args: t.Dict[str, t.Any] = args
  92        self.parent: t.Optional[Expression] = None
  93        self.arg_key: t.Optional[str] = None
  94        self.comments: t.Optional[t.List[str]] = None
  95        self._type: t.Optional[DataType] = None
  96        self._meta: t.Optional[t.Dict[str, t.Any]] = None
  97        self._hash: t.Optional[int] = None
  98
  99        for arg_key, value in self.args.items():
 100            self._set_parent(arg_key, value)
 101
 102    def __eq__(self, other) -> bool:
 103        return type(self) is type(other) and hash(self) == hash(other)
 104
 105    @property
 106    def hashable_args(self) -> t.Any:
 107        return frozenset(
 108            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 109            for k, v in self.args.items()
 110            if not (v is None or v is False or (type(v) is list and not v))
 111        )
 112
 113    def __hash__(self) -> int:
 114        if self._hash is not None:
 115            return self._hash
 116
 117        return hash((self.__class__, self.hashable_args))
 118
 119    @property
 120    def this(self):
 121        """
 122        Retrieves the argument with key "this".
 123        """
 124        return self.args.get("this")
 125
 126    @property
 127    def expression(self):
 128        """
 129        Retrieves the argument with key "expression".
 130        """
 131        return self.args.get("expression")
 132
 133    @property
 134    def expressions(self):
 135        """
 136        Retrieves the argument with key "expressions".
 137        """
 138        return self.args.get("expressions") or []
 139
 140    def text(self, key) -> str:
 141        """
 142        Returns a textual representation of the argument corresponding to "key". This can only be used
 143        for args that are strings or leaf Expression instances, such as identifiers and literals.
 144        """
 145        field = self.args.get(key)
 146        if isinstance(field, str):
 147            return field
 148        if isinstance(field, (Identifier, Literal, Var)):
 149            return field.this
 150        if isinstance(field, (Star, Null)):
 151            return field.name
 152        return ""
 153
 154    @property
 155    def is_string(self) -> bool:
 156        """
 157        Checks whether a Literal expression is a string.
 158        """
 159        return isinstance(self, Literal) and self.args["is_string"]
 160
 161    @property
 162    def is_number(self) -> bool:
 163        """
 164        Checks whether a Literal expression is a number.
 165        """
 166        return isinstance(self, Literal) and not self.args["is_string"]
 167
 168    @property
 169    def is_int(self) -> bool:
 170        """
 171        Checks whether a Literal expression is an integer.
 172        """
 173        if self.is_number:
 174            try:
 175                int(self.name)
 176                return True
 177            except ValueError:
 178                pass
 179        return False
 180
 181    @property
 182    def is_star(self) -> bool:
 183        """Checks whether an expression is a star."""
 184        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 185
 186    @property
 187    def alias(self) -> str:
 188        """
 189        Returns the alias of the expression, or an empty string if it's not aliased.
 190        """
 191        if isinstance(self.args.get("alias"), TableAlias):
 192            return self.args["alias"].name
 193        return self.text("alias")
 194
 195    @property
 196    def alias_column_names(self) -> t.List[str]:
 197        table_alias = self.args.get("alias")
 198        if not table_alias:
 199            return []
 200        return [c.name for c in table_alias.args.get("columns") or []]
 201
 202    @property
 203    def name(self) -> str:
 204        return self.text("this")
 205
 206    @property
 207    def alias_or_name(self) -> str:
 208        return self.alias or self.name
 209
 210    @property
 211    def output_name(self) -> str:
 212        """
 213        Name of the output column if this expression is a selection.
 214
 215        If the Expression has no output name, an empty string is returned.
 216
 217        Example:
 218            >>> from sqlglot import parse_one
 219            >>> parse_one("SELECT a").expressions[0].output_name
 220            'a'
 221            >>> parse_one("SELECT b AS c").expressions[0].output_name
 222            'c'
 223            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 224            ''
 225        """
 226        return ""
 227
 228    @property
 229    def type(self) -> t.Optional[DataType]:
 230        return self._type
 231
 232    @type.setter
 233    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 234        if dtype and not isinstance(dtype, DataType):
 235            dtype = DataType.build(dtype)
 236        self._type = dtype  # type: ignore
 237
 238    @property
 239    def meta(self) -> t.Dict[str, t.Any]:
 240        if self._meta is None:
 241            self._meta = {}
 242        return self._meta
 243
 244    def __deepcopy__(self, memo):
 245        copy = self.__class__(**deepcopy(self.args))
 246        if self.comments is not None:
 247            copy.comments = deepcopy(self.comments)
 248
 249        if self._type is not None:
 250            copy._type = self._type.copy()
 251
 252        if self._meta is not None:
 253            copy._meta = deepcopy(self._meta)
 254
 255        return copy
 256
 257    def copy(self):
 258        """
 259        Returns a deep copy of the expression.
 260        """
 261        new = deepcopy(self)
 262        new.parent = self.parent
 263        return new
 264
 265    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 266        if self.comments is None:
 267            self.comments = []
 268        if comments:
 269            self.comments.extend(comments)
 270
 271    def append(self, arg_key: str, value: t.Any) -> None:
 272        """
 273        Appends value to arg_key if it's a list or sets it as a new list.
 274
 275        Args:
 276            arg_key (str): name of the list expression arg
 277            value (Any): value to append to the list
 278        """
 279        if not isinstance(self.args.get(arg_key), list):
 280            self.args[arg_key] = []
 281        self.args[arg_key].append(value)
 282        self._set_parent(arg_key, value)
 283
 284    def set(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Sets arg_key to value.
 287
 288        Args:
 289            arg_key: name of the expression arg.
 290            value: value to set the arg to.
 291        """
 292        if value is None:
 293            self.args.pop(arg_key, None)
 294            return
 295
 296        self.args[arg_key] = value
 297        self._set_parent(arg_key, value)
 298
 299    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 300        if hasattr(value, "parent"):
 301            value.parent = self
 302            value.arg_key = arg_key
 303        elif type(value) is list:
 304            for v in value:
 305                if hasattr(v, "parent"):
 306                    v.parent = self
 307                    v.arg_key = arg_key
 308
 309    @property
 310    def depth(self) -> int:
 311        """
 312        Returns the depth of this tree.
 313        """
 314        if self.parent:
 315            return self.parent.depth + 1
 316        return 0
 317
 318    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 319        """Yields the key and expression for all arguments, exploding list args."""
 320        for k, vs in self.args.items():
 321            if type(vs) is list:
 322                for v in vs:
 323                    if hasattr(v, "parent"):
 324                        yield k, v
 325            else:
 326                if hasattr(vs, "parent"):
 327                    yield k, vs
 328
 329    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 330        """
 331        Returns the first node in this tree which matches at least one of
 332        the specified types.
 333
 334        Args:
 335            expression_types: the expression type(s) to match.
 336            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 337
 338        Returns:
 339            The node which matches the criteria or None if no such node was found.
 340        """
 341        return next(self.find_all(*expression_types, bfs=bfs), None)
 342
 343    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 344        """
 345        Returns a generator object which visits all nodes in this tree and only
 346        yields those that match at least one of the specified expression types.
 347
 348        Args:
 349            expression_types: the expression type(s) to match.
 350            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 351
 352        Returns:
 353            The generator object.
 354        """
 355        for expression, *_ in self.walk(bfs=bfs):
 356            if isinstance(expression, expression_types):
 357                yield expression
 358
 359    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 360        """
 361        Returns a nearest parent matching expression_types.
 362
 363        Args:
 364            expression_types: the expression type(s) to match.
 365
 366        Returns:
 367            The parent node.
 368        """
 369        ancestor = self.parent
 370        while ancestor and not isinstance(ancestor, expression_types):
 371            ancestor = ancestor.parent
 372        return t.cast(E, ancestor)
 373
 374    @property
 375    def parent_select(self) -> t.Optional[Select]:
 376        """
 377        Returns the parent select statement.
 378        """
 379        return self.find_ancestor(Select)
 380
 381    @property
 382    def same_parent(self) -> bool:
 383        """Returns if the parent is the same class as itself."""
 384        return type(self.parent) is self.__class__
 385
 386    def root(self) -> Expression:
 387        """
 388        Returns the root expression of this tree.
 389        """
 390        expression = self
 391        while expression.parent:
 392            expression = expression.parent
 393        return expression
 394
 395    def walk(self, bfs=True, prune=None):
 396        """
 397        Returns a generator object which visits all nodes in this tree.
 398
 399        Args:
 400            bfs (bool): if set to True the BFS traversal order will be applied,
 401                otherwise the DFS traversal will be used instead.
 402            prune ((node, parent, arg_key) -> bool): callable that returns True if
 403                the generator should stop traversing this branch of the tree.
 404
 405        Returns:
 406            the generator object.
 407        """
 408        if bfs:
 409            yield from self.bfs(prune=prune)
 410        else:
 411            yield from self.dfs(prune=prune)
 412
 413    def dfs(self, parent=None, key=None, prune=None):
 414        """
 415        Returns a generator object which visits all nodes in this tree in
 416        the DFS (Depth-first) order.
 417
 418        Returns:
 419            The generator object.
 420        """
 421        parent = parent or self.parent
 422        yield self, parent, key
 423        if prune and prune(self, parent, key):
 424            return
 425
 426        for k, v in self.iter_expressions():
 427            yield from v.dfs(self, k, prune)
 428
 429    def bfs(self, prune=None):
 430        """
 431        Returns a generator object which visits all nodes in this tree in
 432        the BFS (Breadth-first) order.
 433
 434        Returns:
 435            The generator object.
 436        """
 437        queue = deque([(self, self.parent, None)])
 438
 439        while queue:
 440            item, parent, key = queue.popleft()
 441
 442            yield item, parent, key
 443            if prune and prune(item, parent, key):
 444                continue
 445
 446            for k, v in item.iter_expressions():
 447                queue.append((v, item, k))
 448
 449    def unnest(self):
 450        """
 451        Returns the first non parenthesis child or self.
 452        """
 453        expression = self
 454        while type(expression) is Paren:
 455            expression = expression.this
 456        return expression
 457
 458    def unalias(self):
 459        """
 460        Returns the inner expression if this is an Alias.
 461        """
 462        if isinstance(self, Alias):
 463            return self.this
 464        return self
 465
 466    def unnest_operands(self):
 467        """
 468        Returns unnested operands as a tuple.
 469        """
 470        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 471
 472    def flatten(self, unnest=True):
 473        """
 474        Returns a generator which yields child nodes who's parents are the same class.
 475
 476        A AND B AND C -> [A, B, C]
 477        """
 478        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 479            if not type(node) is self.__class__:
 480                yield node.unnest() if unnest else node
 481
 482    def __str__(self) -> str:
 483        return self.sql()
 484
 485    def __repr__(self) -> str:
 486        return self._to_s()
 487
 488    def sql(self, dialect: DialectType = None, **opts) -> str:
 489        """
 490        Returns SQL string representation of this tree.
 491
 492        Args:
 493            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 494            opts: other `sqlglot.generator.Generator` options.
 495
 496        Returns:
 497            The SQL string.
 498        """
 499        from sqlglot.dialects import Dialect
 500
 501        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 502
 503    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 504        indent = "" if not level else "\n"
 505        indent += "".join(["  "] * level)
 506        left = f"({self.key.upper()} "
 507
 508        args: t.Dict[str, t.Any] = {
 509            k: ", ".join(
 510                v._to_s(hide_missing=hide_missing, level=level + 1)
 511                if hasattr(v, "_to_s")
 512                else str(v)
 513                for v in ensure_list(vs)
 514                if v is not None
 515            )
 516            for k, vs in self.args.items()
 517        }
 518        args["comments"] = self.comments
 519        args["type"] = self.type
 520        args = {k: v for k, v in args.items() if v or not hide_missing}
 521
 522        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 523        right += ")"
 524
 525        return indent + left + right
 526
 527    def transform(self, fun, *args, copy=True, **kwargs):
 528        """
 529        Recursively visits all tree nodes (excluding already transformed ones)
 530        and applies the given transformation function to each node.
 531
 532        Args:
 533            fun (function): a function which takes a node as an argument and returns a
 534                new transformed node or the same node without modifications. If the function
 535                returns None, then the corresponding node will be removed from the syntax tree.
 536            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 537                modified in place.
 538
 539        Returns:
 540            The transformed tree.
 541        """
 542        node = self.copy() if copy else self
 543        new_node = fun(node, *args, **kwargs)
 544
 545        if new_node is None or not isinstance(new_node, Expression):
 546            return new_node
 547        if new_node is not node:
 548            new_node.parent = node.parent
 549            return new_node
 550
 551        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 552        return new_node
 553
 554    @t.overload
 555    def replace(self, expression: E) -> E:
 556        ...
 557
 558    @t.overload
 559    def replace(self, expression: None) -> None:
 560        ...
 561
 562    def replace(self, expression):
 563        """
 564        Swap out this expression with a new expression.
 565
 566        For example::
 567
 568            >>> tree = Select().select("x").from_("tbl")
 569            >>> tree.find(Column).replace(Column(this="y"))
 570            (COLUMN this: y)
 571            >>> tree.sql()
 572            'SELECT y FROM tbl'
 573
 574        Args:
 575            expression: new node
 576
 577        Returns:
 578            The new expression or expressions.
 579        """
 580        if not self.parent:
 581            return expression
 582
 583        parent = self.parent
 584        self.parent = None
 585
 586        replace_children(parent, lambda child: expression if child is self else child)
 587        return expression
 588
 589    def pop(self: E) -> E:
 590        """
 591        Remove this expression from its AST.
 592
 593        Returns:
 594            The popped expression.
 595        """
 596        self.replace(None)
 597        return self
 598
 599    def assert_is(self, type_: t.Type[E]) -> E:
 600        """
 601        Assert that this `Expression` is an instance of `type_`.
 602
 603        If it is NOT an instance of `type_`, this raises an assertion error.
 604        Otherwise, this returns this expression.
 605
 606        Examples:
 607            This is useful for type security in chained expressions:
 608
 609            >>> import sqlglot
 610            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 611            'SELECT x, z FROM y'
 612        """
 613        assert isinstance(self, type_)
 614        return self
 615
 616    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 617        """
 618        Checks if this expression is valid (e.g. all mandatory args are set).
 619
 620        Args:
 621            args: a sequence of values that were used to instantiate a Func expression. This is used
 622                to check that the provided arguments don't exceed the function argument limit.
 623
 624        Returns:
 625            A list of error messages for all possible errors that were found.
 626        """
 627        errors: t.List[str] = []
 628
 629        for k in self.args:
 630            if k not in self.arg_types:
 631                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 632        for k, mandatory in self.arg_types.items():
 633            v = self.args.get(k)
 634            if mandatory and (v is None or (isinstance(v, list) and not v)):
 635                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 636
 637        if (
 638            args
 639            and isinstance(self, Func)
 640            and len(args) > len(self.arg_types)
 641            and not self.is_var_len_args
 642        ):
 643            errors.append(
 644                f"The number of provided arguments ({len(args)}) is greater than "
 645                f"the maximum number of supported arguments ({len(self.arg_types)})"
 646            )
 647
 648        return errors
 649
 650    def dump(self):
 651        """
 652        Dump this Expression to a JSON-serializable dict.
 653        """
 654        from sqlglot.serde import dump
 655
 656        return dump(self)
 657
 658    @classmethod
 659    def load(cls, obj):
 660        """
 661        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 662        """
 663        from sqlglot.serde import load
 664
 665        return load(obj)
 666
 667
 668IntoType = t.Union[
 669    str,
 670    t.Type[Expression],
 671    t.Collection[t.Union[str, t.Type[Expression]]],
 672]
 673ExpOrStr = t.Union[str, Expression]
 674
 675
 676class Condition(Expression):
 677    def and_(
 678        self,
 679        *expressions: t.Optional[ExpOrStr],
 680        dialect: DialectType = None,
 681        copy: bool = True,
 682        **opts,
 683    ) -> Condition:
 684        """
 685        AND this condition with one or multiple expressions.
 686
 687        Example:
 688            >>> condition("x=1").and_("y=1").sql()
 689            'x = 1 AND y = 1'
 690
 691        Args:
 692            *expressions: the SQL code strings to parse.
 693                If an `Expression` instance is passed, it will be used as-is.
 694            dialect: the dialect used to parse the input expression.
 695            copy: whether or not to copy the involved expressions (only applies to Expressions).
 696            opts: other options to use to parse the input expressions.
 697
 698        Returns:
 699            The new And condition.
 700        """
 701        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 702
 703    def or_(
 704        self,
 705        *expressions: t.Optional[ExpOrStr],
 706        dialect: DialectType = None,
 707        copy: bool = True,
 708        **opts,
 709    ) -> Condition:
 710        """
 711        OR this condition with one or multiple expressions.
 712
 713        Example:
 714            >>> condition("x=1").or_("y=1").sql()
 715            'x = 1 OR y = 1'
 716
 717        Args:
 718            *expressions: the SQL code strings to parse.
 719                If an `Expression` instance is passed, it will be used as-is.
 720            dialect: the dialect used to parse the input expression.
 721            copy: whether or not to copy the involved expressions (only applies to Expressions).
 722            opts: other options to use to parse the input expressions.
 723
 724        Returns:
 725            The new Or condition.
 726        """
 727        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 728
 729    def not_(self, copy: bool = True):
 730        """
 731        Wrap this condition with NOT.
 732
 733        Example:
 734            >>> condition("x=1").not_().sql()
 735            'NOT x = 1'
 736
 737        Args:
 738            copy: whether or not to copy this object.
 739
 740        Returns:
 741            The new Not instance.
 742        """
 743        return not_(self, copy=copy)
 744
 745    def as_(
 746        self,
 747        alias: str | Identifier,
 748        quoted: t.Optional[bool] = None,
 749        dialect: DialectType = None,
 750        copy: bool = True,
 751        **opts,
 752    ) -> Alias:
 753        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 754
 755    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 756        this = self.copy()
 757        other = convert(other, copy=True)
 758        if not isinstance(this, klass) and not isinstance(other, klass):
 759            this = _wrap(this, Binary)
 760            other = _wrap(other, Binary)
 761        if reverse:
 762            return klass(this=other, expression=this)
 763        return klass(this=this, expression=other)
 764
 765    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]):
 766        return Bracket(
 767            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 768        )
 769
 770    def isin(
 771        self,
 772        *expressions: t.Any,
 773        query: t.Optional[ExpOrStr] = None,
 774        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 775        copy: bool = True,
 776        **opts,
 777    ) -> In:
 778        return In(
 779            this=maybe_copy(self, copy),
 780            expressions=[convert(e, copy=copy) for e in expressions],
 781            query=maybe_parse(query, copy=copy, **opts) if query else None,
 782            unnest=Unnest(
 783                expressions=[
 784                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 785                ]
 786            )
 787            if unnest
 788            else None,
 789        )
 790
 791    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 792        return Between(
 793            this=maybe_copy(self, copy),
 794            low=convert(low, copy=copy, **opts),
 795            high=convert(high, copy=copy, **opts),
 796        )
 797
 798    def is_(self, other: ExpOrStr) -> Is:
 799        return self._binop(Is, other)
 800
 801    def like(self, other: ExpOrStr) -> Like:
 802        return self._binop(Like, other)
 803
 804    def ilike(self, other: ExpOrStr) -> ILike:
 805        return self._binop(ILike, other)
 806
 807    def eq(self, other: t.Any) -> EQ:
 808        return self._binop(EQ, other)
 809
 810    def neq(self, other: t.Any) -> NEQ:
 811        return self._binop(NEQ, other)
 812
 813    def rlike(self, other: ExpOrStr) -> RegexpLike:
 814        return self._binop(RegexpLike, other)
 815
 816    def __lt__(self, other: t.Any) -> LT:
 817        return self._binop(LT, other)
 818
 819    def __le__(self, other: t.Any) -> LTE:
 820        return self._binop(LTE, other)
 821
 822    def __gt__(self, other: t.Any) -> GT:
 823        return self._binop(GT, other)
 824
 825    def __ge__(self, other: t.Any) -> GTE:
 826        return self._binop(GTE, other)
 827
 828    def __add__(self, other: t.Any) -> Add:
 829        return self._binop(Add, other)
 830
 831    def __radd__(self, other: t.Any) -> Add:
 832        return self._binop(Add, other, reverse=True)
 833
 834    def __sub__(self, other: t.Any) -> Sub:
 835        return self._binop(Sub, other)
 836
 837    def __rsub__(self, other: t.Any) -> Sub:
 838        return self._binop(Sub, other, reverse=True)
 839
 840    def __mul__(self, other: t.Any) -> Mul:
 841        return self._binop(Mul, other)
 842
 843    def __rmul__(self, other: t.Any) -> Mul:
 844        return self._binop(Mul, other, reverse=True)
 845
 846    def __truediv__(self, other: t.Any) -> Div:
 847        return self._binop(Div, other)
 848
 849    def __rtruediv__(self, other: t.Any) -> Div:
 850        return self._binop(Div, other, reverse=True)
 851
 852    def __floordiv__(self, other: t.Any) -> IntDiv:
 853        return self._binop(IntDiv, other)
 854
 855    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 856        return self._binop(IntDiv, other, reverse=True)
 857
 858    def __mod__(self, other: t.Any) -> Mod:
 859        return self._binop(Mod, other)
 860
 861    def __rmod__(self, other: t.Any) -> Mod:
 862        return self._binop(Mod, other, reverse=True)
 863
 864    def __pow__(self, other: t.Any) -> Pow:
 865        return self._binop(Pow, other)
 866
 867    def __rpow__(self, other: t.Any) -> Pow:
 868        return self._binop(Pow, other, reverse=True)
 869
 870    def __and__(self, other: t.Any) -> And:
 871        return self._binop(And, other)
 872
 873    def __rand__(self, other: t.Any) -> And:
 874        return self._binop(And, other, reverse=True)
 875
 876    def __or__(self, other: t.Any) -> Or:
 877        return self._binop(Or, other)
 878
 879    def __ror__(self, other: t.Any) -> Or:
 880        return self._binop(Or, other, reverse=True)
 881
 882    def __neg__(self) -> Neg:
 883        return Neg(this=_wrap(self.copy(), Binary))
 884
 885    def __invert__(self) -> Not:
 886        return not_(self.copy())
 887
 888
 889class Predicate(Condition):
 890    """Relationships like x = y, x > 1, x >= y."""
 891
 892
 893class DerivedTable(Expression):
 894    @property
 895    def selects(self) -> t.List[Expression]:
 896        return self.this.selects if isinstance(self.this, Subqueryable) else []
 897
 898    @property
 899    def named_selects(self) -> t.List[str]:
 900        return [select.output_name for select in self.selects]
 901
 902
 903class Unionable(Expression):
 904    def union(
 905        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 906    ) -> Unionable:
 907        """
 908        Builds a UNION expression.
 909
 910        Example:
 911            >>> import sqlglot
 912            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 913            'SELECT * FROM foo UNION SELECT * FROM bla'
 914
 915        Args:
 916            expression: the SQL code string.
 917                If an `Expression` instance is passed, it will be used as-is.
 918            distinct: set the DISTINCT flag if and only if this is true.
 919            dialect: the dialect used to parse the input expression.
 920            opts: other options to use to parse the input expressions.
 921
 922        Returns:
 923            The new Union expression.
 924        """
 925        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 926
 927    def intersect(
 928        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 929    ) -> Unionable:
 930        """
 931        Builds an INTERSECT expression.
 932
 933        Example:
 934            >>> import sqlglot
 935            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 936            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 937
 938        Args:
 939            expression: the SQL code string.
 940                If an `Expression` instance is passed, it will be used as-is.
 941            distinct: set the DISTINCT flag if and only if this is true.
 942            dialect: the dialect used to parse the input expression.
 943            opts: other options to use to parse the input expressions.
 944
 945        Returns:
 946            The new Intersect expression.
 947        """
 948        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 949
 950    def except_(
 951        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 952    ) -> Unionable:
 953        """
 954        Builds an EXCEPT expression.
 955
 956        Example:
 957            >>> import sqlglot
 958            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 959            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 960
 961        Args:
 962            expression: the SQL code string.
 963                If an `Expression` instance is passed, it will be used as-is.
 964            distinct: set the DISTINCT flag if and only if this is true.
 965            dialect: the dialect used to parse the input expression.
 966            opts: other options to use to parse the input expressions.
 967
 968        Returns:
 969            The new Except expression.
 970        """
 971        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 972
 973
 974class UDTF(DerivedTable, Unionable):
 975    @property
 976    def selects(self) -> t.List[Expression]:
 977        alias = self.args.get("alias")
 978        return alias.columns if alias else []
 979
 980
 981class Cache(Expression):
 982    arg_types = {
 983        "with": False,
 984        "this": True,
 985        "lazy": False,
 986        "options": False,
 987        "expression": False,
 988    }
 989
 990
 991class Uncache(Expression):
 992    arg_types = {"this": True, "exists": False}
 993
 994
 995class DDL(Expression):
 996    @property
 997    def ctes(self):
 998        with_ = self.args.get("with")
 999        if not with_:
1000            return []
1001        return with_.expressions
1002
1003    @property
1004    def named_selects(self) -> t.List[str]:
1005        if isinstance(self.expression, Subqueryable):
1006            return self.expression.named_selects
1007        return []
1008
1009    @property
1010    def selects(self) -> t.List[Expression]:
1011        if isinstance(self.expression, Subqueryable):
1012            return self.expression.selects
1013        return []
1014
1015
1016class Create(DDL):
1017    arg_types = {
1018        "with": False,
1019        "this": True,
1020        "kind": True,
1021        "expression": False,
1022        "exists": False,
1023        "properties": False,
1024        "replace": False,
1025        "unique": False,
1026        "indexes": False,
1027        "no_schema_binding": False,
1028        "begin": False,
1029        "clone": False,
1030    }
1031
1032
1033# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1034class Clone(Expression):
1035    arg_types = {
1036        "this": True,
1037        "when": False,
1038        "kind": False,
1039        "shallow": False,
1040        "expression": False,
1041    }
1042
1043
1044class Describe(Expression):
1045    arg_types = {"this": True, "kind": False, "expressions": False}
1046
1047
1048class Pragma(Expression):
1049    pass
1050
1051
1052class Set(Expression):
1053    arg_types = {"expressions": False, "unset": False, "tag": False}
1054
1055
1056class SetItem(Expression):
1057    arg_types = {
1058        "this": False,
1059        "expressions": False,
1060        "kind": False,
1061        "collate": False,  # MySQL SET NAMES statement
1062        "global": False,
1063    }
1064
1065
1066class Show(Expression):
1067    arg_types = {
1068        "this": True,
1069        "target": False,
1070        "offset": False,
1071        "limit": False,
1072        "like": False,
1073        "where": False,
1074        "db": False,
1075        "scope": False,
1076        "scope_kind": False,
1077        "full": False,
1078        "mutex": False,
1079        "query": False,
1080        "channel": False,
1081        "global": False,
1082        "log": False,
1083        "position": False,
1084        "types": False,
1085    }
1086
1087
1088class UserDefinedFunction(Expression):
1089    arg_types = {"this": True, "expressions": False, "wrapped": False}
1090
1091
1092class CharacterSet(Expression):
1093    arg_types = {"this": True, "default": False}
1094
1095
1096class With(Expression):
1097    arg_types = {"expressions": True, "recursive": False}
1098
1099    @property
1100    def recursive(self) -> bool:
1101        return bool(self.args.get("recursive"))
1102
1103
1104class WithinGroup(Expression):
1105    arg_types = {"this": True, "expression": False}
1106
1107
1108class CTE(DerivedTable):
1109    arg_types = {"this": True, "alias": True}
1110
1111
1112class TableAlias(Expression):
1113    arg_types = {"this": False, "columns": False}
1114
1115    @property
1116    def columns(self):
1117        return self.args.get("columns") or []
1118
1119
1120class BitString(Condition):
1121    pass
1122
1123
1124class HexString(Condition):
1125    pass
1126
1127
1128class ByteString(Condition):
1129    pass
1130
1131
1132class RawString(Condition):
1133    pass
1134
1135
1136class Column(Condition):
1137    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1138
1139    @property
1140    def table(self) -> str:
1141        return self.text("table")
1142
1143    @property
1144    def db(self) -> str:
1145        return self.text("db")
1146
1147    @property
1148    def catalog(self) -> str:
1149        return self.text("catalog")
1150
1151    @property
1152    def output_name(self) -> str:
1153        return self.name
1154
1155    @property
1156    def parts(self) -> t.List[Identifier]:
1157        """Return the parts of a column in order catalog, db, table, name."""
1158        return [
1159            t.cast(Identifier, self.args[part])
1160            for part in ("catalog", "db", "table", "this")
1161            if self.args.get(part)
1162        ]
1163
1164    def to_dot(self) -> Dot:
1165        """Converts the column into a dot expression."""
1166        parts = self.parts
1167        parent = self.parent
1168
1169        while parent:
1170            if isinstance(parent, Dot):
1171                parts.append(parent.expression)
1172            parent = parent.parent
1173
1174        return Dot.build(deepcopy(parts))
1175
1176
1177class ColumnPosition(Expression):
1178    arg_types = {"this": False, "position": True}
1179
1180
1181class ColumnDef(Expression):
1182    arg_types = {
1183        "this": True,
1184        "kind": False,
1185        "constraints": False,
1186        "exists": False,
1187        "position": False,
1188    }
1189
1190    @property
1191    def constraints(self) -> t.List[ColumnConstraint]:
1192        return self.args.get("constraints") or []
1193
1194
1195class AlterColumn(Expression):
1196    arg_types = {
1197        "this": True,
1198        "dtype": False,
1199        "collate": False,
1200        "using": False,
1201        "default": False,
1202        "drop": False,
1203    }
1204
1205
1206class RenameTable(Expression):
1207    pass
1208
1209
1210class Comment(Expression):
1211    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1212
1213
1214class Comprehension(Expression):
1215    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1216
1217
1218# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1219class MergeTreeTTLAction(Expression):
1220    arg_types = {
1221        "this": True,
1222        "delete": False,
1223        "recompress": False,
1224        "to_disk": False,
1225        "to_volume": False,
1226    }
1227
1228
1229# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1230class MergeTreeTTL(Expression):
1231    arg_types = {
1232        "expressions": True,
1233        "where": False,
1234        "group": False,
1235        "aggregates": False,
1236    }
1237
1238
1239# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1240class IndexConstraintOption(Expression):
1241    arg_types = {
1242        "key_block_size": False,
1243        "using": False,
1244        "parser": False,
1245        "comment": False,
1246        "visible": False,
1247        "engine_attr": False,
1248        "secondary_engine_attr": False,
1249    }
1250
1251
1252class ColumnConstraint(Expression):
1253    arg_types = {"this": False, "kind": True}
1254
1255    @property
1256    def kind(self) -> ColumnConstraintKind:
1257        return self.args["kind"]
1258
1259
1260class ColumnConstraintKind(Expression):
1261    pass
1262
1263
1264class AutoIncrementColumnConstraint(ColumnConstraintKind):
1265    pass
1266
1267
1268class CaseSpecificColumnConstraint(ColumnConstraintKind):
1269    arg_types = {"not_": True}
1270
1271
1272class CharacterSetColumnConstraint(ColumnConstraintKind):
1273    arg_types = {"this": True}
1274
1275
1276class CheckColumnConstraint(ColumnConstraintKind):
1277    pass
1278
1279
1280class ClusteredColumnConstraint(ColumnConstraintKind):
1281    pass
1282
1283
1284class CollateColumnConstraint(ColumnConstraintKind):
1285    pass
1286
1287
1288class CommentColumnConstraint(ColumnConstraintKind):
1289    pass
1290
1291
1292class CompressColumnConstraint(ColumnConstraintKind):
1293    pass
1294
1295
1296class DateFormatColumnConstraint(ColumnConstraintKind):
1297    arg_types = {"this": True}
1298
1299
1300class DefaultColumnConstraint(ColumnConstraintKind):
1301    pass
1302
1303
1304class EncodeColumnConstraint(ColumnConstraintKind):
1305    pass
1306
1307
1308class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1309    # this: True -> ALWAYS, this: False -> BY DEFAULT
1310    arg_types = {
1311        "this": False,
1312        "expression": False,
1313        "on_null": False,
1314        "start": False,
1315        "increment": False,
1316        "minvalue": False,
1317        "maxvalue": False,
1318        "cycle": False,
1319    }
1320
1321
1322# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1323class IndexColumnConstraint(ColumnConstraintKind):
1324    arg_types = {
1325        "this": False,
1326        "schema": True,
1327        "kind": False,
1328        "index_type": False,
1329        "options": False,
1330    }
1331
1332
1333class InlineLengthColumnConstraint(ColumnConstraintKind):
1334    pass
1335
1336
1337class NonClusteredColumnConstraint(ColumnConstraintKind):
1338    pass
1339
1340
1341class NotForReplicationColumnConstraint(ColumnConstraintKind):
1342    arg_types = {}
1343
1344
1345class NotNullColumnConstraint(ColumnConstraintKind):
1346    arg_types = {"allow_null": False}
1347
1348
1349# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1350class OnUpdateColumnConstraint(ColumnConstraintKind):
1351    pass
1352
1353
1354class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1355    arg_types = {"desc": False}
1356
1357
1358class TitleColumnConstraint(ColumnConstraintKind):
1359    pass
1360
1361
1362class UniqueColumnConstraint(ColumnConstraintKind):
1363    arg_types = {"this": False, "index_type": False}
1364
1365
1366class UppercaseColumnConstraint(ColumnConstraintKind):
1367    arg_types: t.Dict[str, t.Any] = {}
1368
1369
1370class PathColumnConstraint(ColumnConstraintKind):
1371    pass
1372
1373
1374# computed column expression
1375# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1376class ComputedColumnConstraint(ColumnConstraintKind):
1377    arg_types = {"this": True, "persisted": False, "not_null": False}
1378
1379
1380class Constraint(Expression):
1381    arg_types = {"this": True, "expressions": True}
1382
1383
1384class Delete(Expression):
1385    arg_types = {
1386        "with": False,
1387        "this": False,
1388        "using": False,
1389        "where": False,
1390        "returning": False,
1391        "limit": False,
1392        "tables": False,  # Multiple-Table Syntax (MySQL)
1393    }
1394
1395    def delete(
1396        self,
1397        table: ExpOrStr,
1398        dialect: DialectType = None,
1399        copy: bool = True,
1400        **opts,
1401    ) -> Delete:
1402        """
1403        Create a DELETE expression or replace the table on an existing DELETE expression.
1404
1405        Example:
1406            >>> delete("tbl").sql()
1407            'DELETE FROM tbl'
1408
1409        Args:
1410            table: the table from which to delete.
1411            dialect: the dialect used to parse the input expression.
1412            copy: if `False`, modify this expression instance in-place.
1413            opts: other options to use to parse the input expressions.
1414
1415        Returns:
1416            Delete: the modified expression.
1417        """
1418        return _apply_builder(
1419            expression=table,
1420            instance=self,
1421            arg="this",
1422            dialect=dialect,
1423            into=Table,
1424            copy=copy,
1425            **opts,
1426        )
1427
1428    def where(
1429        self,
1430        *expressions: t.Optional[ExpOrStr],
1431        append: bool = True,
1432        dialect: DialectType = None,
1433        copy: bool = True,
1434        **opts,
1435    ) -> Delete:
1436        """
1437        Append to or set the WHERE expressions.
1438
1439        Example:
1440            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1441            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1442
1443        Args:
1444            *expressions: the SQL code strings to parse.
1445                If an `Expression` instance is passed, it will be used as-is.
1446                Multiple expressions are combined with an AND operator.
1447            append: if `True`, AND the new expressions to any existing expression.
1448                Otherwise, this resets the expression.
1449            dialect: the dialect used to parse the input expressions.
1450            copy: if `False`, modify this expression instance in-place.
1451            opts: other options to use to parse the input expressions.
1452
1453        Returns:
1454            Delete: the modified expression.
1455        """
1456        return _apply_conjunction_builder(
1457            *expressions,
1458            instance=self,
1459            arg="where",
1460            append=append,
1461            into=Where,
1462            dialect=dialect,
1463            copy=copy,
1464            **opts,
1465        )
1466
1467    def returning(
1468        self,
1469        expression: ExpOrStr,
1470        dialect: DialectType = None,
1471        copy: bool = True,
1472        **opts,
1473    ) -> Delete:
1474        """
1475        Set the RETURNING expression. Not supported by all dialects.
1476
1477        Example:
1478            >>> delete("tbl").returning("*", dialect="postgres").sql()
1479            'DELETE FROM tbl RETURNING *'
1480
1481        Args:
1482            expression: the SQL code strings to parse.
1483                If an `Expression` instance is passed, it will be used as-is.
1484            dialect: the dialect used to parse the input expressions.
1485            copy: if `False`, modify this expression instance in-place.
1486            opts: other options to use to parse the input expressions.
1487
1488        Returns:
1489            Delete: the modified expression.
1490        """
1491        return _apply_builder(
1492            expression=expression,
1493            instance=self,
1494            arg="returning",
1495            prefix="RETURNING",
1496            dialect=dialect,
1497            copy=copy,
1498            into=Returning,
1499            **opts,
1500        )
1501
1502
1503class Drop(Expression):
1504    arg_types = {
1505        "this": False,
1506        "kind": False,
1507        "exists": False,
1508        "temporary": False,
1509        "materialized": False,
1510        "cascade": False,
1511        "constraints": False,
1512        "purge": False,
1513    }
1514
1515
1516class Filter(Expression):
1517    arg_types = {"this": True, "expression": True}
1518
1519
1520class Check(Expression):
1521    pass
1522
1523
1524# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1525class Connect(Expression):
1526    arg_types = {"start": False, "connect": True}
1527
1528
1529class Prior(Expression):
1530    pass
1531
1532
1533class Directory(Expression):
1534    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1535    arg_types = {"this": True, "local": False, "row_format": False}
1536
1537
1538class ForeignKey(Expression):
1539    arg_types = {
1540        "expressions": True,
1541        "reference": False,
1542        "delete": False,
1543        "update": False,
1544    }
1545
1546
1547class ColumnPrefix(Expression):
1548    arg_types = {"this": True, "expression": True}
1549
1550
1551class PrimaryKey(Expression):
1552    arg_types = {"expressions": True, "options": False}
1553
1554
1555# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1556# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1557class Into(Expression):
1558    arg_types = {"this": True, "temporary": False, "unlogged": False}
1559
1560
1561class From(Expression):
1562    @property
1563    def name(self) -> str:
1564        return self.this.name
1565
1566    @property
1567    def alias_or_name(self) -> str:
1568        return self.this.alias_or_name
1569
1570
1571class Having(Expression):
1572    pass
1573
1574
1575class Hint(Expression):
1576    arg_types = {"expressions": True}
1577
1578
1579class JoinHint(Expression):
1580    arg_types = {"this": True, "expressions": True}
1581
1582
1583class Identifier(Expression):
1584    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1585
1586    @property
1587    def quoted(self) -> bool:
1588        return bool(self.args.get("quoted"))
1589
1590    @property
1591    def hashable_args(self) -> t.Any:
1592        return (self.this, self.quoted)
1593
1594    @property
1595    def output_name(self) -> str:
1596        return self.name
1597
1598
1599class Index(Expression):
1600    arg_types = {
1601        "this": False,
1602        "table": False,
1603        "using": False,
1604        "where": False,
1605        "columns": False,
1606        "unique": False,
1607        "primary": False,
1608        "amp": False,  # teradata
1609        "partition_by": False,  # teradata
1610    }
1611
1612
1613class Insert(DDL):
1614    arg_types = {
1615        "with": False,
1616        "this": True,
1617        "expression": False,
1618        "conflict": False,
1619        "returning": False,
1620        "overwrite": False,
1621        "exists": False,
1622        "partition": False,
1623        "alternative": False,
1624        "where": False,
1625        "ignore": False,
1626        "by_name": False,
1627    }
1628
1629    def with_(
1630        self,
1631        alias: ExpOrStr,
1632        as_: ExpOrStr,
1633        recursive: t.Optional[bool] = None,
1634        append: bool = True,
1635        dialect: DialectType = None,
1636        copy: bool = True,
1637        **opts,
1638    ) -> Insert:
1639        """
1640        Append to or set the common table expressions.
1641
1642        Example:
1643            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1644            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1645
1646        Args:
1647            alias: the SQL code string to parse as the table name.
1648                If an `Expression` instance is passed, this is used as-is.
1649            as_: the SQL code string to parse as the table expression.
1650                If an `Expression` instance is passed, it will be used as-is.
1651            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1652            append: if `True`, add to any existing expressions.
1653                Otherwise, this resets the expressions.
1654            dialect: the dialect used to parse the input expression.
1655            copy: if `False`, modify this expression instance in-place.
1656            opts: other options to use to parse the input expressions.
1657
1658        Returns:
1659            The modified expression.
1660        """
1661        return _apply_cte_builder(
1662            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1663        )
1664
1665
1666class OnConflict(Expression):
1667    arg_types = {
1668        "duplicate": False,
1669        "expressions": False,
1670        "nothing": False,
1671        "key": False,
1672        "constraint": False,
1673    }
1674
1675
1676class Returning(Expression):
1677    arg_types = {"expressions": True, "into": False}
1678
1679
1680# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1681class Introducer(Expression):
1682    arg_types = {"this": True, "expression": True}
1683
1684
1685# national char, like n'utf8'
1686class National(Expression):
1687    pass
1688
1689
1690class LoadData(Expression):
1691    arg_types = {
1692        "this": True,
1693        "local": False,
1694        "overwrite": False,
1695        "inpath": True,
1696        "partition": False,
1697        "input_format": False,
1698        "serde": False,
1699    }
1700
1701
1702class Partition(Expression):
1703    arg_types = {"expressions": True}
1704
1705
1706class Fetch(Expression):
1707    arg_types = {
1708        "direction": False,
1709        "count": False,
1710        "percent": False,
1711        "with_ties": False,
1712    }
1713
1714
1715class Group(Expression):
1716    arg_types = {
1717        "expressions": False,
1718        "grouping_sets": False,
1719        "cube": False,
1720        "rollup": False,
1721        "totals": False,
1722        "all": False,
1723    }
1724
1725
1726class Lambda(Expression):
1727    arg_types = {"this": True, "expressions": True}
1728
1729
1730class Limit(Expression):
1731    arg_types = {"this": False, "expression": True, "offset": False}
1732
1733
1734class Literal(Condition):
1735    arg_types = {"this": True, "is_string": True}
1736
1737    @property
1738    def hashable_args(self) -> t.Any:
1739        return (self.this, self.args.get("is_string"))
1740
1741    @classmethod
1742    def number(cls, number) -> Literal:
1743        return cls(this=str(number), is_string=False)
1744
1745    @classmethod
1746    def string(cls, string) -> Literal:
1747        return cls(this=str(string), is_string=True)
1748
1749    @property
1750    def output_name(self) -> str:
1751        return self.name
1752
1753
1754class Join(Expression):
1755    arg_types = {
1756        "this": True,
1757        "on": False,
1758        "side": False,
1759        "kind": False,
1760        "using": False,
1761        "method": False,
1762        "global": False,
1763        "hint": False,
1764    }
1765
1766    @property
1767    def method(self) -> str:
1768        return self.text("method").upper()
1769
1770    @property
1771    def kind(self) -> str:
1772        return self.text("kind").upper()
1773
1774    @property
1775    def side(self) -> str:
1776        return self.text("side").upper()
1777
1778    @property
1779    def hint(self) -> str:
1780        return self.text("hint").upper()
1781
1782    @property
1783    def alias_or_name(self) -> str:
1784        return self.this.alias_or_name
1785
1786    def on(
1787        self,
1788        *expressions: t.Optional[ExpOrStr],
1789        append: bool = True,
1790        dialect: DialectType = None,
1791        copy: bool = True,
1792        **opts,
1793    ) -> Join:
1794        """
1795        Append to or set the ON expressions.
1796
1797        Example:
1798            >>> import sqlglot
1799            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1800            'JOIN x ON y = 1'
1801
1802        Args:
1803            *expressions: the SQL code strings to parse.
1804                If an `Expression` instance is passed, it will be used as-is.
1805                Multiple expressions are combined with an AND operator.
1806            append: if `True`, AND the new expressions to any existing expression.
1807                Otherwise, this resets the expression.
1808            dialect: the dialect used to parse the input expressions.
1809            copy: if `False`, modify this expression instance in-place.
1810            opts: other options to use to parse the input expressions.
1811
1812        Returns:
1813            The modified Join expression.
1814        """
1815        join = _apply_conjunction_builder(
1816            *expressions,
1817            instance=self,
1818            arg="on",
1819            append=append,
1820            dialect=dialect,
1821            copy=copy,
1822            **opts,
1823        )
1824
1825        if join.kind == "CROSS":
1826            join.set("kind", None)
1827
1828        return join
1829
1830    def using(
1831        self,
1832        *expressions: t.Optional[ExpOrStr],
1833        append: bool = True,
1834        dialect: DialectType = None,
1835        copy: bool = True,
1836        **opts,
1837    ) -> Join:
1838        """
1839        Append to or set the USING expressions.
1840
1841        Example:
1842            >>> import sqlglot
1843            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1844            'JOIN x USING (foo, bla)'
1845
1846        Args:
1847            *expressions: the SQL code strings to parse.
1848                If an `Expression` instance is passed, it will be used as-is.
1849            append: if `True`, concatenate the new expressions to the existing "using" list.
1850                Otherwise, this resets the expression.
1851            dialect: the dialect used to parse the input expressions.
1852            copy: if `False`, modify this expression instance in-place.
1853            opts: other options to use to parse the input expressions.
1854
1855        Returns:
1856            The modified Join expression.
1857        """
1858        join = _apply_list_builder(
1859            *expressions,
1860            instance=self,
1861            arg="using",
1862            append=append,
1863            dialect=dialect,
1864            copy=copy,
1865            **opts,
1866        )
1867
1868        if join.kind == "CROSS":
1869            join.set("kind", None)
1870
1871        return join
1872
1873
1874class Lateral(UDTF):
1875    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1876
1877
1878class MatchRecognize(Expression):
1879    arg_types = {
1880        "partition_by": False,
1881        "order": False,
1882        "measures": False,
1883        "rows": False,
1884        "after": False,
1885        "pattern": False,
1886        "define": False,
1887        "alias": False,
1888    }
1889
1890
1891# Clickhouse FROM FINAL modifier
1892# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1893class Final(Expression):
1894    pass
1895
1896
1897class Offset(Expression):
1898    arg_types = {"this": False, "expression": True}
1899
1900
1901class Order(Expression):
1902    arg_types = {"this": False, "expressions": True}
1903
1904
1905# hive specific sorts
1906# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1907class Cluster(Order):
1908    pass
1909
1910
1911class Distribute(Order):
1912    pass
1913
1914
1915class Sort(Order):
1916    pass
1917
1918
1919class Ordered(Expression):
1920    arg_types = {"this": True, "desc": True, "nulls_first": True}
1921
1922
1923class Property(Expression):
1924    arg_types = {"this": True, "value": True}
1925
1926
1927class AlgorithmProperty(Property):
1928    arg_types = {"this": True}
1929
1930
1931class AutoIncrementProperty(Property):
1932    arg_types = {"this": True}
1933
1934
1935class BlockCompressionProperty(Property):
1936    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1937
1938
1939class CharacterSetProperty(Property):
1940    arg_types = {"this": True, "default": True}
1941
1942
1943class ChecksumProperty(Property):
1944    arg_types = {"on": False, "default": False}
1945
1946
1947class CollateProperty(Property):
1948    arg_types = {"this": True}
1949
1950
1951class CopyGrantsProperty(Property):
1952    arg_types = {}
1953
1954
1955class DataBlocksizeProperty(Property):
1956    arg_types = {
1957        "size": False,
1958        "units": False,
1959        "minimum": False,
1960        "maximum": False,
1961        "default": False,
1962    }
1963
1964
1965class DefinerProperty(Property):
1966    arg_types = {"this": True}
1967
1968
1969class DistKeyProperty(Property):
1970    arg_types = {"this": True}
1971
1972
1973class DistStyleProperty(Property):
1974    arg_types = {"this": True}
1975
1976
1977class EngineProperty(Property):
1978    arg_types = {"this": True}
1979
1980
1981class HeapProperty(Property):
1982    arg_types = {}
1983
1984
1985class ToTableProperty(Property):
1986    arg_types = {"this": True}
1987
1988
1989class ExecuteAsProperty(Property):
1990    arg_types = {"this": True}
1991
1992
1993class ExternalProperty(Property):
1994    arg_types = {"this": False}
1995
1996
1997class FallbackProperty(Property):
1998    arg_types = {"no": True, "protection": False}
1999
2000
2001class FileFormatProperty(Property):
2002    arg_types = {"this": True}
2003
2004
2005class FreespaceProperty(Property):
2006    arg_types = {"this": True, "percent": False}
2007
2008
2009class InputOutputFormat(Expression):
2010    arg_types = {"input_format": False, "output_format": False}
2011
2012
2013class IsolatedLoadingProperty(Property):
2014    arg_types = {
2015        "no": True,
2016        "concurrent": True,
2017        "for_all": True,
2018        "for_insert": True,
2019        "for_none": True,
2020    }
2021
2022
2023class JournalProperty(Property):
2024    arg_types = {
2025        "no": False,
2026        "dual": False,
2027        "before": False,
2028        "local": False,
2029        "after": False,
2030    }
2031
2032
2033class LanguageProperty(Property):
2034    arg_types = {"this": True}
2035
2036
2037# spark ddl
2038class ClusteredByProperty(Property):
2039    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2040
2041
2042class DictProperty(Property):
2043    arg_types = {"this": True, "kind": True, "settings": False}
2044
2045
2046class DictSubProperty(Property):
2047    pass
2048
2049
2050class DictRange(Property):
2051    arg_types = {"this": True, "min": True, "max": True}
2052
2053
2054# Clickhouse CREATE ... ON CLUSTER modifier
2055# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2056class OnCluster(Property):
2057    arg_types = {"this": True}
2058
2059
2060class LikeProperty(Property):
2061    arg_types = {"this": True, "expressions": False}
2062
2063
2064class LocationProperty(Property):
2065    arg_types = {"this": True}
2066
2067
2068class LockingProperty(Property):
2069    arg_types = {
2070        "this": False,
2071        "kind": True,
2072        "for_or_in": True,
2073        "lock_type": True,
2074        "override": False,
2075    }
2076
2077
2078class LogProperty(Property):
2079    arg_types = {"no": True}
2080
2081
2082class MaterializedProperty(Property):
2083    arg_types = {"this": False}
2084
2085
2086class MergeBlockRatioProperty(Property):
2087    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2088
2089
2090class NoPrimaryIndexProperty(Property):
2091    arg_types = {}
2092
2093
2094class OnProperty(Property):
2095    arg_types = {"this": True}
2096
2097
2098class OnCommitProperty(Property):
2099    arg_types = {"delete": False}
2100
2101
2102class PartitionedByProperty(Property):
2103    arg_types = {"this": True}
2104
2105
2106class ReturnsProperty(Property):
2107    arg_types = {"this": True, "is_table": False, "table": False}
2108
2109
2110class RowFormatProperty(Property):
2111    arg_types = {"this": True}
2112
2113
2114class RowFormatDelimitedProperty(Property):
2115    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2116    arg_types = {
2117        "fields": False,
2118        "escaped": False,
2119        "collection_items": False,
2120        "map_keys": False,
2121        "lines": False,
2122        "null": False,
2123        "serde": False,
2124    }
2125
2126
2127class RowFormatSerdeProperty(Property):
2128    arg_types = {"this": True, "serde_properties": False}
2129
2130
2131# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2132class QueryTransform(Expression):
2133    arg_types = {
2134        "expressions": True,
2135        "command_script": True,
2136        "schema": False,
2137        "row_format_before": False,
2138        "record_writer": False,
2139        "row_format_after": False,
2140        "record_reader": False,
2141    }
2142
2143
2144class SchemaCommentProperty(Property):
2145    arg_types = {"this": True}
2146
2147
2148class SerdeProperties(Property):
2149    arg_types = {"expressions": True}
2150
2151
2152class SetProperty(Property):
2153    arg_types = {"multi": True}
2154
2155
2156class SettingsProperty(Property):
2157    arg_types = {"expressions": True}
2158
2159
2160class SortKeyProperty(Property):
2161    arg_types = {"this": True, "compound": False}
2162
2163
2164class SqlSecurityProperty(Property):
2165    arg_types = {"definer": True}
2166
2167
2168class StabilityProperty(Property):
2169    arg_types = {"this": True}
2170
2171
2172class TemporaryProperty(Property):
2173    arg_types = {}
2174
2175
2176class TransientProperty(Property):
2177    arg_types = {"this": False}
2178
2179
2180class VolatileProperty(Property):
2181    arg_types = {"this": False}
2182
2183
2184class WithDataProperty(Property):
2185    arg_types = {"no": True, "statistics": False}
2186
2187
2188class WithJournalTableProperty(Property):
2189    arg_types = {"this": True}
2190
2191
2192class Properties(Expression):
2193    arg_types = {"expressions": True}
2194
2195    NAME_TO_PROPERTY = {
2196        "ALGORITHM": AlgorithmProperty,
2197        "AUTO_INCREMENT": AutoIncrementProperty,
2198        "CHARACTER SET": CharacterSetProperty,
2199        "CLUSTERED_BY": ClusteredByProperty,
2200        "COLLATE": CollateProperty,
2201        "COMMENT": SchemaCommentProperty,
2202        "DEFINER": DefinerProperty,
2203        "DISTKEY": DistKeyProperty,
2204        "DISTSTYLE": DistStyleProperty,
2205        "ENGINE": EngineProperty,
2206        "EXECUTE AS": ExecuteAsProperty,
2207        "FORMAT": FileFormatProperty,
2208        "LANGUAGE": LanguageProperty,
2209        "LOCATION": LocationProperty,
2210        "PARTITIONED_BY": PartitionedByProperty,
2211        "RETURNS": ReturnsProperty,
2212        "ROW_FORMAT": RowFormatProperty,
2213        "SORTKEY": SortKeyProperty,
2214    }
2215
2216    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2217
2218    # CREATE property locations
2219    # Form: schema specified
2220    #   create [POST_CREATE]
2221    #     table a [POST_NAME]
2222    #     (b int) [POST_SCHEMA]
2223    #     with ([POST_WITH])
2224    #     index (b) [POST_INDEX]
2225    #
2226    # Form: alias selection
2227    #   create [POST_CREATE]
2228    #     table a [POST_NAME]
2229    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2230    #     index (c) [POST_INDEX]
2231    class Location(AutoName):
2232        POST_CREATE = auto()
2233        POST_NAME = auto()
2234        POST_SCHEMA = auto()
2235        POST_WITH = auto()
2236        POST_ALIAS = auto()
2237        POST_EXPRESSION = auto()
2238        POST_INDEX = auto()
2239        UNSUPPORTED = auto()
2240
2241    @classmethod
2242    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2243        expressions = []
2244        for key, value in properties_dict.items():
2245            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2246            if property_cls:
2247                expressions.append(property_cls(this=convert(value)))
2248            else:
2249                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2250
2251        return cls(expressions=expressions)
2252
2253
2254class Qualify(Expression):
2255    pass
2256
2257
2258# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2259class Return(Expression):
2260    pass
2261
2262
2263class Reference(Expression):
2264    arg_types = {"this": True, "expressions": False, "options": False}
2265
2266
2267class Tuple(Expression):
2268    arg_types = {"expressions": False}
2269
2270    def isin(
2271        self,
2272        *expressions: t.Any,
2273        query: t.Optional[ExpOrStr] = None,
2274        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2275        copy: bool = True,
2276        **opts,
2277    ) -> In:
2278        return In(
2279            this=maybe_copy(self, copy),
2280            expressions=[convert(e, copy=copy) for e in expressions],
2281            query=maybe_parse(query, copy=copy, **opts) if query else None,
2282            unnest=Unnest(
2283                expressions=[
2284                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2285                ]
2286            )
2287            if unnest
2288            else None,
2289        )
2290
2291
2292class Subqueryable(Unionable):
2293    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2294        """
2295        Convert this expression to an aliased expression that can be used as a Subquery.
2296
2297        Example:
2298            >>> subquery = Select().select("x").from_("tbl").subquery()
2299            >>> Select().select("x").from_(subquery).sql()
2300            'SELECT x FROM (SELECT x FROM tbl)'
2301
2302        Args:
2303            alias (str | Identifier): an optional alias for the subquery
2304            copy (bool): if `False`, modify this expression instance in-place.
2305
2306        Returns:
2307            Alias: the subquery
2308        """
2309        instance = maybe_copy(self, copy)
2310        if not isinstance(alias, Expression):
2311            alias = TableAlias(this=to_identifier(alias)) if alias else None
2312
2313        return Subquery(this=instance, alias=alias)
2314
2315    def limit(
2316        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2317    ) -> Select:
2318        raise NotImplementedError
2319
2320    @property
2321    def ctes(self):
2322        with_ = self.args.get("with")
2323        if not with_:
2324            return []
2325        return with_.expressions
2326
2327    @property
2328    def selects(self) -> t.List[Expression]:
2329        raise NotImplementedError("Subqueryable objects must implement `selects`")
2330
2331    @property
2332    def named_selects(self) -> t.List[str]:
2333        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2334
2335    def select(
2336        self,
2337        *expressions: t.Optional[ExpOrStr],
2338        append: bool = True,
2339        dialect: DialectType = None,
2340        copy: bool = True,
2341        **opts,
2342    ) -> Subqueryable:
2343        raise NotImplementedError("Subqueryable objects must implement `select`")
2344
2345    def with_(
2346        self,
2347        alias: ExpOrStr,
2348        as_: ExpOrStr,
2349        recursive: t.Optional[bool] = None,
2350        append: bool = True,
2351        dialect: DialectType = None,
2352        copy: bool = True,
2353        **opts,
2354    ) -> Subqueryable:
2355        """
2356        Append to or set the common table expressions.
2357
2358        Example:
2359            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2360            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2361
2362        Args:
2363            alias: the SQL code string to parse as the table name.
2364                If an `Expression` instance is passed, this is used as-is.
2365            as_: the SQL code string to parse as the table expression.
2366                If an `Expression` instance is passed, it will be used as-is.
2367            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2368            append: if `True`, add to any existing expressions.
2369                Otherwise, this resets the expressions.
2370            dialect: the dialect used to parse the input expression.
2371            copy: if `False`, modify this expression instance in-place.
2372            opts: other options to use to parse the input expressions.
2373
2374        Returns:
2375            The modified expression.
2376        """
2377        return _apply_cte_builder(
2378            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2379        )
2380
2381
2382QUERY_MODIFIERS = {
2383    "match": False,
2384    "laterals": False,
2385    "joins": False,
2386    "connect": False,
2387    "pivots": False,
2388    "where": False,
2389    "group": False,
2390    "having": False,
2391    "qualify": False,
2392    "windows": False,
2393    "distribute": False,
2394    "sort": False,
2395    "cluster": False,
2396    "order": False,
2397    "limit": False,
2398    "offset": False,
2399    "locks": False,
2400    "sample": False,
2401    "settings": False,
2402    "format": False,
2403}
2404
2405
2406# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2407class WithTableHint(Expression):
2408    arg_types = {"expressions": True}
2409
2410
2411# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2412class IndexTableHint(Expression):
2413    arg_types = {"this": True, "expressions": False, "target": False}
2414
2415
2416class Table(Expression):
2417    arg_types = {
2418        "this": True,
2419        "alias": False,
2420        "db": False,
2421        "catalog": False,
2422        "laterals": False,
2423        "joins": False,
2424        "pivots": False,
2425        "hints": False,
2426        "system_time": False,
2427        "version": False,
2428    }
2429
2430    @property
2431    def name(self) -> str:
2432        if isinstance(self.this, Func):
2433            return ""
2434        return self.this.name
2435
2436    @property
2437    def db(self) -> str:
2438        return self.text("db")
2439
2440    @property
2441    def catalog(self) -> str:
2442        return self.text("catalog")
2443
2444    @property
2445    def selects(self) -> t.List[Expression]:
2446        return []
2447
2448    @property
2449    def named_selects(self) -> t.List[str]:
2450        return []
2451
2452    @property
2453    def parts(self) -> t.List[Identifier]:
2454        """Return the parts of a table in order catalog, db, table."""
2455        parts: t.List[Identifier] = []
2456
2457        for arg in ("catalog", "db", "this"):
2458            part = self.args.get(arg)
2459
2460            if isinstance(part, Identifier):
2461                parts.append(part)
2462            elif isinstance(part, Dot):
2463                parts.extend(part.flatten())
2464
2465        return parts
2466
2467
2468class Union(Subqueryable):
2469    arg_types = {
2470        "with": False,
2471        "this": True,
2472        "expression": True,
2473        "distinct": False,
2474        "by_name": False,
2475        **QUERY_MODIFIERS,
2476    }
2477
2478    def limit(
2479        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2480    ) -> Select:
2481        """
2482        Set the LIMIT expression.
2483
2484        Example:
2485            >>> select("1").union(select("1")).limit(1).sql()
2486            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2487
2488        Args:
2489            expression: the SQL code string to parse.
2490                This can also be an integer.
2491                If a `Limit` instance is passed, this is used as-is.
2492                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2493            dialect: the dialect used to parse the input expression.
2494            copy: if `False`, modify this expression instance in-place.
2495            opts: other options to use to parse the input expressions.
2496
2497        Returns:
2498            The limited subqueryable.
2499        """
2500        return (
2501            select("*")
2502            .from_(self.subquery(alias="_l_0", copy=copy))
2503            .limit(expression, dialect=dialect, copy=False, **opts)
2504        )
2505
2506    def select(
2507        self,
2508        *expressions: t.Optional[ExpOrStr],
2509        append: bool = True,
2510        dialect: DialectType = None,
2511        copy: bool = True,
2512        **opts,
2513    ) -> Union:
2514        """Append to or set the SELECT of the union recursively.
2515
2516        Example:
2517            >>> from sqlglot import parse_one
2518            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2519            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2520
2521        Args:
2522            *expressions: the SQL code strings to parse.
2523                If an `Expression` instance is passed, it will be used as-is.
2524            append: if `True`, add to any existing expressions.
2525                Otherwise, this resets the expressions.
2526            dialect: the dialect used to parse the input expressions.
2527            copy: if `False`, modify this expression instance in-place.
2528            opts: other options to use to parse the input expressions.
2529
2530        Returns:
2531            Union: the modified expression.
2532        """
2533        this = self.copy() if copy else self
2534        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2535        this.expression.unnest().select(
2536            *expressions, append=append, dialect=dialect, copy=False, **opts
2537        )
2538        return this
2539
2540    @property
2541    def named_selects(self) -> t.List[str]:
2542        return self.this.unnest().named_selects
2543
2544    @property
2545    def is_star(self) -> bool:
2546        return self.this.is_star or self.expression.is_star
2547
2548    @property
2549    def selects(self) -> t.List[Expression]:
2550        return self.this.unnest().selects
2551
2552    @property
2553    def left(self):
2554        return self.this
2555
2556    @property
2557    def right(self):
2558        return self.expression
2559
2560
2561class Except(Union):
2562    pass
2563
2564
2565class Intersect(Union):
2566    pass
2567
2568
2569class Unnest(UDTF):
2570    arg_types = {
2571        "expressions": True,
2572        "ordinality": False,
2573        "alias": False,
2574        "offset": False,
2575    }
2576
2577
2578class Update(Expression):
2579    arg_types = {
2580        "with": False,
2581        "this": False,
2582        "expressions": True,
2583        "from": False,
2584        "where": False,
2585        "returning": False,
2586        "order": False,
2587        "limit": False,
2588    }
2589
2590
2591class Values(UDTF):
2592    arg_types = {
2593        "expressions": True,
2594        "ordinality": False,
2595        "alias": False,
2596    }
2597
2598
2599class Var(Expression):
2600    pass
2601
2602
2603class Version(Expression):
2604    """
2605    Time travel, iceberg, bigquery etc
2606    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2607    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2608    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2609    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2610    this is either TIMESTAMP or VERSION
2611    kind is ("AS OF", "BETWEEN")
2612    """
2613
2614    arg_types = {"this": True, "kind": True, "expression": False}
2615
2616
2617class Schema(Expression):
2618    arg_types = {"this": False, "expressions": False}
2619
2620
2621# https://dev.mysql.com/doc/refman/8.0/en/select.html
2622# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2623class Lock(Expression):
2624    arg_types = {"update": True, "expressions": False, "wait": False}
2625
2626
2627class Select(Subqueryable):
2628    arg_types = {
2629        "with": False,
2630        "kind": False,
2631        "expressions": False,
2632        "hint": False,
2633        "distinct": False,
2634        "into": False,
2635        "from": False,
2636        **QUERY_MODIFIERS,
2637    }
2638
2639    def from_(
2640        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2641    ) -> Select:
2642        """
2643        Set the FROM expression.
2644
2645        Example:
2646            >>> Select().from_("tbl").select("x").sql()
2647            'SELECT x FROM tbl'
2648
2649        Args:
2650            expression : the SQL code strings to parse.
2651                If a `From` instance is passed, this is used as-is.
2652                If another `Expression` instance is passed, it will be wrapped in a `From`.
2653            dialect: the dialect used to parse the input expression.
2654            copy: if `False`, modify this expression instance in-place.
2655            opts: other options to use to parse the input expressions.
2656
2657        Returns:
2658            The modified Select expression.
2659        """
2660        return _apply_builder(
2661            expression=expression,
2662            instance=self,
2663            arg="from",
2664            into=From,
2665            prefix="FROM",
2666            dialect=dialect,
2667            copy=copy,
2668            **opts,
2669        )
2670
2671    def group_by(
2672        self,
2673        *expressions: t.Optional[ExpOrStr],
2674        append: bool = True,
2675        dialect: DialectType = None,
2676        copy: bool = True,
2677        **opts,
2678    ) -> Select:
2679        """
2680        Set the GROUP BY expression.
2681
2682        Example:
2683            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2684            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2685
2686        Args:
2687            *expressions: the SQL code strings to parse.
2688                If a `Group` instance is passed, this is used as-is.
2689                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2690                If nothing is passed in then a group by is not applied to the expression
2691            append: if `True`, add to any existing expressions.
2692                Otherwise, this flattens all the `Group` expression into a single expression.
2693            dialect: the dialect used to parse the input expression.
2694            copy: if `False`, modify this expression instance in-place.
2695            opts: other options to use to parse the input expressions.
2696
2697        Returns:
2698            The modified Select expression.
2699        """
2700        if not expressions:
2701            return self if not copy else self.copy()
2702
2703        return _apply_child_list_builder(
2704            *expressions,
2705            instance=self,
2706            arg="group",
2707            append=append,
2708            copy=copy,
2709            prefix="GROUP BY",
2710            into=Group,
2711            dialect=dialect,
2712            **opts,
2713        )
2714
2715    def order_by(
2716        self,
2717        *expressions: t.Optional[ExpOrStr],
2718        append: bool = True,
2719        dialect: DialectType = None,
2720        copy: bool = True,
2721        **opts,
2722    ) -> Select:
2723        """
2724        Set the ORDER BY expression.
2725
2726        Example:
2727            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2728            'SELECT x FROM tbl ORDER BY x DESC'
2729
2730        Args:
2731            *expressions: the SQL code strings to parse.
2732                If a `Group` instance is passed, this is used as-is.
2733                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2734            append: if `True`, add to any existing expressions.
2735                Otherwise, this flattens all the `Order` expression into a single expression.
2736            dialect: the dialect used to parse the input expression.
2737            copy: if `False`, modify this expression instance in-place.
2738            opts: other options to use to parse the input expressions.
2739
2740        Returns:
2741            The modified Select expression.
2742        """
2743        return _apply_child_list_builder(
2744            *expressions,
2745            instance=self,
2746            arg="order",
2747            append=append,
2748            copy=copy,
2749            prefix="ORDER BY",
2750            into=Order,
2751            dialect=dialect,
2752            **opts,
2753        )
2754
2755    def sort_by(
2756        self,
2757        *expressions: t.Optional[ExpOrStr],
2758        append: bool = True,
2759        dialect: DialectType = None,
2760        copy: bool = True,
2761        **opts,
2762    ) -> Select:
2763        """
2764        Set the SORT BY expression.
2765
2766        Example:
2767            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2768            'SELECT x FROM tbl SORT BY x DESC'
2769
2770        Args:
2771            *expressions: the SQL code strings to parse.
2772                If a `Group` instance is passed, this is used as-is.
2773                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2774            append: if `True`, add to any existing expressions.
2775                Otherwise, this flattens all the `Order` expression into a single expression.
2776            dialect: the dialect used to parse the input expression.
2777            copy: if `False`, modify this expression instance in-place.
2778            opts: other options to use to parse the input expressions.
2779
2780        Returns:
2781            The modified Select expression.
2782        """
2783        return _apply_child_list_builder(
2784            *expressions,
2785            instance=self,
2786            arg="sort",
2787            append=append,
2788            copy=copy,
2789            prefix="SORT BY",
2790            into=Sort,
2791            dialect=dialect,
2792            **opts,
2793        )
2794
2795    def cluster_by(
2796        self,
2797        *expressions: t.Optional[ExpOrStr],
2798        append: bool = True,
2799        dialect: DialectType = None,
2800        copy: bool = True,
2801        **opts,
2802    ) -> Select:
2803        """
2804        Set the CLUSTER BY expression.
2805
2806        Example:
2807            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2808            'SELECT x FROM tbl CLUSTER BY x DESC'
2809
2810        Args:
2811            *expressions: the SQL code strings to parse.
2812                If a `Group` instance is passed, this is used as-is.
2813                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2814            append: if `True`, add to any existing expressions.
2815                Otherwise, this flattens all the `Order` expression into a single expression.
2816            dialect: the dialect used to parse the input expression.
2817            copy: if `False`, modify this expression instance in-place.
2818            opts: other options to use to parse the input expressions.
2819
2820        Returns:
2821            The modified Select expression.
2822        """
2823        return _apply_child_list_builder(
2824            *expressions,
2825            instance=self,
2826            arg="cluster",
2827            append=append,
2828            copy=copy,
2829            prefix="CLUSTER BY",
2830            into=Cluster,
2831            dialect=dialect,
2832            **opts,
2833        )
2834
2835    def limit(
2836        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2837    ) -> Select:
2838        """
2839        Set the LIMIT expression.
2840
2841        Example:
2842            >>> Select().from_("tbl").select("x").limit(10).sql()
2843            'SELECT x FROM tbl LIMIT 10'
2844
2845        Args:
2846            expression: the SQL code string to parse.
2847                This can also be an integer.
2848                If a `Limit` instance is passed, this is used as-is.
2849                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2850            dialect: the dialect used to parse the input expression.
2851            copy: if `False`, modify this expression instance in-place.
2852            opts: other options to use to parse the input expressions.
2853
2854        Returns:
2855            Select: the modified expression.
2856        """
2857        return _apply_builder(
2858            expression=expression,
2859            instance=self,
2860            arg="limit",
2861            into=Limit,
2862            prefix="LIMIT",
2863            dialect=dialect,
2864            copy=copy,
2865            **opts,
2866        )
2867
2868    def offset(
2869        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2870    ) -> Select:
2871        """
2872        Set the OFFSET expression.
2873
2874        Example:
2875            >>> Select().from_("tbl").select("x").offset(10).sql()
2876            'SELECT x FROM tbl OFFSET 10'
2877
2878        Args:
2879            expression: the SQL code string to parse.
2880                This can also be an integer.
2881                If a `Offset` instance is passed, this is used as-is.
2882                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2883            dialect: the dialect used to parse the input expression.
2884            copy: if `False`, modify this expression instance in-place.
2885            opts: other options to use to parse the input expressions.
2886
2887        Returns:
2888            The modified Select expression.
2889        """
2890        return _apply_builder(
2891            expression=expression,
2892            instance=self,
2893            arg="offset",
2894            into=Offset,
2895            prefix="OFFSET",
2896            dialect=dialect,
2897            copy=copy,
2898            **opts,
2899        )
2900
2901    def select(
2902        self,
2903        *expressions: t.Optional[ExpOrStr],
2904        append: bool = True,
2905        dialect: DialectType = None,
2906        copy: bool = True,
2907        **opts,
2908    ) -> Select:
2909        """
2910        Append to or set the SELECT expressions.
2911
2912        Example:
2913            >>> Select().select("x", "y").sql()
2914            'SELECT x, y'
2915
2916        Args:
2917            *expressions: the SQL code strings to parse.
2918                If an `Expression` instance is passed, it will be used as-is.
2919            append: if `True`, add to any existing expressions.
2920                Otherwise, this resets the expressions.
2921            dialect: the dialect used to parse the input expressions.
2922            copy: if `False`, modify this expression instance in-place.
2923            opts: other options to use to parse the input expressions.
2924
2925        Returns:
2926            The modified Select expression.
2927        """
2928        return _apply_list_builder(
2929            *expressions,
2930            instance=self,
2931            arg="expressions",
2932            append=append,
2933            dialect=dialect,
2934            copy=copy,
2935            **opts,
2936        )
2937
2938    def lateral(
2939        self,
2940        *expressions: t.Optional[ExpOrStr],
2941        append: bool = True,
2942        dialect: DialectType = None,
2943        copy: bool = True,
2944        **opts,
2945    ) -> Select:
2946        """
2947        Append to or set the LATERAL expressions.
2948
2949        Example:
2950            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
2951            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
2952
2953        Args:
2954            *expressions: the SQL code strings to parse.
2955                If an `Expression` instance is passed, it will be used as-is.
2956            append: if `True`, add to any existing expressions.
2957                Otherwise, this resets the expressions.
2958            dialect: the dialect used to parse the input expressions.
2959            copy: if `False`, modify this expression instance in-place.
2960            opts: other options to use to parse the input expressions.
2961
2962        Returns:
2963            The modified Select expression.
2964        """
2965        return _apply_list_builder(
2966            *expressions,
2967            instance=self,
2968            arg="laterals",
2969            append=append,
2970            into=Lateral,
2971            prefix="LATERAL VIEW",
2972            dialect=dialect,
2973            copy=copy,
2974            **opts,
2975        )
2976
2977    def join(
2978        self,
2979        expression: ExpOrStr,
2980        on: t.Optional[ExpOrStr] = None,
2981        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
2982        append: bool = True,
2983        join_type: t.Optional[str] = None,
2984        join_alias: t.Optional[Identifier | str] = None,
2985        dialect: DialectType = None,
2986        copy: bool = True,
2987        **opts,
2988    ) -> Select:
2989        """
2990        Append to or set the JOIN expressions.
2991
2992        Example:
2993            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
2994            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
2995
2996            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
2997            'SELECT 1 FROM a JOIN b USING (x, y, z)'
2998
2999            Use `join_type` to change the type of join:
3000
3001            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3002            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3003
3004        Args:
3005            expression: the SQL code string to parse.
3006                If an `Expression` instance is passed, it will be used as-is.
3007            on: optionally specify the join "on" criteria as a SQL string.
3008                If an `Expression` instance is passed, it will be used as-is.
3009            using: optionally specify the join "using" criteria as a SQL string.
3010                If an `Expression` instance is passed, it will be used as-is.
3011            append: if `True`, add to any existing expressions.
3012                Otherwise, this resets the expressions.
3013            join_type: if set, alter the parsed join type.
3014            join_alias: an optional alias for the joined source.
3015            dialect: the dialect used to parse the input expressions.
3016            copy: if `False`, modify this expression instance in-place.
3017            opts: other options to use to parse the input expressions.
3018
3019        Returns:
3020            Select: the modified expression.
3021        """
3022        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3023
3024        try:
3025            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3026        except ParseError:
3027            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3028
3029        join = expression if isinstance(expression, Join) else Join(this=expression)
3030
3031        if isinstance(join.this, Select):
3032            join.this.replace(join.this.subquery())
3033
3034        if join_type:
3035            method: t.Optional[Token]
3036            side: t.Optional[Token]
3037            kind: t.Optional[Token]
3038
3039            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3040
3041            if method:
3042                join.set("method", method.text)
3043            if side:
3044                join.set("side", side.text)
3045            if kind:
3046                join.set("kind", kind.text)
3047
3048        if on:
3049            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3050            join.set("on", on)
3051
3052        if using:
3053            join = _apply_list_builder(
3054                *ensure_list(using),
3055                instance=join,
3056                arg="using",
3057                append=append,
3058                copy=copy,
3059                into=Identifier,
3060                **opts,
3061            )
3062
3063        if join_alias:
3064            join.set("this", alias_(join.this, join_alias, table=True))
3065
3066        return _apply_list_builder(
3067            join,
3068            instance=self,
3069            arg="joins",
3070            append=append,
3071            copy=copy,
3072            **opts,
3073        )
3074
3075    def where(
3076        self,
3077        *expressions: t.Optional[ExpOrStr],
3078        append: bool = True,
3079        dialect: DialectType = None,
3080        copy: bool = True,
3081        **opts,
3082    ) -> Select:
3083        """
3084        Append to or set the WHERE expressions.
3085
3086        Example:
3087            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3088            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3089
3090        Args:
3091            *expressions: the SQL code strings to parse.
3092                If an `Expression` instance is passed, it will be used as-is.
3093                Multiple expressions are combined with an AND operator.
3094            append: if `True`, AND the new expressions to any existing expression.
3095                Otherwise, this resets the expression.
3096            dialect: the dialect used to parse the input expressions.
3097            copy: if `False`, modify this expression instance in-place.
3098            opts: other options to use to parse the input expressions.
3099
3100        Returns:
3101            Select: the modified expression.
3102        """
3103        return _apply_conjunction_builder(
3104            *expressions,
3105            instance=self,
3106            arg="where",
3107            append=append,
3108            into=Where,
3109            dialect=dialect,
3110            copy=copy,
3111            **opts,
3112        )
3113
3114    def having(
3115        self,
3116        *expressions: t.Optional[ExpOrStr],
3117        append: bool = True,
3118        dialect: DialectType = None,
3119        copy: bool = True,
3120        **opts,
3121    ) -> Select:
3122        """
3123        Append to or set the HAVING expressions.
3124
3125        Example:
3126            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3127            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3128
3129        Args:
3130            *expressions: the SQL code strings to parse.
3131                If an `Expression` instance is passed, it will be used as-is.
3132                Multiple expressions are combined with an AND operator.
3133            append: if `True`, AND the new expressions to any existing expression.
3134                Otherwise, this resets the expression.
3135            dialect: the dialect used to parse the input expressions.
3136            copy: if `False`, modify this expression instance in-place.
3137            opts: other options to use to parse the input expressions.
3138
3139        Returns:
3140            The modified Select expression.
3141        """
3142        return _apply_conjunction_builder(
3143            *expressions,
3144            instance=self,
3145            arg="having",
3146            append=append,
3147            into=Having,
3148            dialect=dialect,
3149            copy=copy,
3150            **opts,
3151        )
3152
3153    def window(
3154        self,
3155        *expressions: t.Optional[ExpOrStr],
3156        append: bool = True,
3157        dialect: DialectType = None,
3158        copy: bool = True,
3159        **opts,
3160    ) -> Select:
3161        return _apply_list_builder(
3162            *expressions,
3163            instance=self,
3164            arg="windows",
3165            append=append,
3166            into=Window,
3167            dialect=dialect,
3168            copy=copy,
3169            **opts,
3170        )
3171
3172    def qualify(
3173        self,
3174        *expressions: t.Optional[ExpOrStr],
3175        append: bool = True,
3176        dialect: DialectType = None,
3177        copy: bool = True,
3178        **opts,
3179    ) -> Select:
3180        return _apply_conjunction_builder(
3181            *expressions,
3182            instance=self,
3183            arg="qualify",
3184            append=append,
3185            into=Qualify,
3186            dialect=dialect,
3187            copy=copy,
3188            **opts,
3189        )
3190
3191    def distinct(
3192        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3193    ) -> Select:
3194        """
3195        Set the OFFSET expression.
3196
3197        Example:
3198            >>> Select().from_("tbl").select("x").distinct().sql()
3199            'SELECT DISTINCT x FROM tbl'
3200
3201        Args:
3202            ons: the expressions to distinct on
3203            distinct: whether the Select should be distinct
3204            copy: if `False`, modify this expression instance in-place.
3205
3206        Returns:
3207            Select: the modified expression.
3208        """
3209        instance = maybe_copy(self, copy)
3210        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3211        instance.set("distinct", Distinct(on=on) if distinct else None)
3212        return instance
3213
3214    def ctas(
3215        self,
3216        table: ExpOrStr,
3217        properties: t.Optional[t.Dict] = None,
3218        dialect: DialectType = None,
3219        copy: bool = True,
3220        **opts,
3221    ) -> Create:
3222        """
3223        Convert this expression to a CREATE TABLE AS statement.
3224
3225        Example:
3226            >>> Select().select("*").from_("tbl").ctas("x").sql()
3227            'CREATE TABLE x AS SELECT * FROM tbl'
3228
3229        Args:
3230            table: the SQL code string to parse as the table name.
3231                If another `Expression` instance is passed, it will be used as-is.
3232            properties: an optional mapping of table properties
3233            dialect: the dialect used to parse the input table.
3234            copy: if `False`, modify this expression instance in-place.
3235            opts: other options to use to parse the input table.
3236
3237        Returns:
3238            The new Create expression.
3239        """
3240        instance = maybe_copy(self, copy)
3241        table_expression = maybe_parse(
3242            table,
3243            into=Table,
3244            dialect=dialect,
3245            **opts,
3246        )
3247        properties_expression = None
3248        if properties:
3249            properties_expression = Properties.from_dict(properties)
3250
3251        return Create(
3252            this=table_expression,
3253            kind="table",
3254            expression=instance,
3255            properties=properties_expression,
3256        )
3257
3258    def lock(self, update: bool = True, copy: bool = True) -> Select:
3259        """
3260        Set the locking read mode for this expression.
3261
3262        Examples:
3263            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3264            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3265
3266            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3267            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3268
3269        Args:
3270            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3271            copy: if `False`, modify this expression instance in-place.
3272
3273        Returns:
3274            The modified expression.
3275        """
3276        inst = maybe_copy(self, copy)
3277        inst.set("locks", [Lock(update=update)])
3278
3279        return inst
3280
3281    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3282        """
3283        Set hints for this expression.
3284
3285        Examples:
3286            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3287            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3288
3289        Args:
3290            hints: The SQL code strings to parse as the hints.
3291                If an `Expression` instance is passed, it will be used as-is.
3292            dialect: The dialect used to parse the hints.
3293            copy: If `False`, modify this expression instance in-place.
3294
3295        Returns:
3296            The modified expression.
3297        """
3298        inst = maybe_copy(self, copy)
3299        inst.set(
3300            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3301        )
3302
3303        return inst
3304
3305    @property
3306    def named_selects(self) -> t.List[str]:
3307        return [e.output_name for e in self.expressions if e.alias_or_name]
3308
3309    @property
3310    def is_star(self) -> bool:
3311        return any(expression.is_star for expression in self.expressions)
3312
3313    @property
3314    def selects(self) -> t.List[Expression]:
3315        return self.expressions
3316
3317
3318class Subquery(DerivedTable, Unionable):
3319    arg_types = {
3320        "this": True,
3321        "alias": False,
3322        "with": False,
3323        **QUERY_MODIFIERS,
3324    }
3325
3326    def unnest(self):
3327        """
3328        Returns the first non subquery.
3329        """
3330        expression = self
3331        while isinstance(expression, Subquery):
3332            expression = expression.this
3333        return expression
3334
3335    def unwrap(self) -> Subquery:
3336        expression = self
3337        while expression.same_parent and expression.is_wrapper:
3338            expression = t.cast(Subquery, expression.parent)
3339        return expression
3340
3341    @property
3342    def is_wrapper(self) -> bool:
3343        """
3344        Whether this Subquery acts as a simple wrapper around another expression.
3345
3346        SELECT * FROM (((SELECT * FROM t)))
3347                      ^
3348                      This corresponds to a "wrapper" Subquery node
3349        """
3350        return all(v is None for k, v in self.args.items() if k != "this")
3351
3352    @property
3353    def is_star(self) -> bool:
3354        return self.this.is_star
3355
3356    @property
3357    def output_name(self) -> str:
3358        return self.alias
3359
3360
3361class TableSample(Expression):
3362    arg_types = {
3363        "this": False,
3364        "expressions": False,
3365        "method": False,
3366        "bucket_numerator": False,
3367        "bucket_denominator": False,
3368        "bucket_field": False,
3369        "percent": False,
3370        "rows": False,
3371        "size": False,
3372        "seed": False,
3373        "kind": False,
3374    }
3375
3376
3377class Tag(Expression):
3378    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3379
3380    arg_types = {
3381        "this": False,
3382        "prefix": False,
3383        "postfix": False,
3384    }
3385
3386
3387# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3388# https://duckdb.org/docs/sql/statements/pivot
3389class Pivot(Expression):
3390    arg_types = {
3391        "this": False,
3392        "alias": False,
3393        "expressions": True,
3394        "field": False,
3395        "unpivot": False,
3396        "using": False,
3397        "group": False,
3398        "columns": False,
3399        "include_nulls": False,
3400    }
3401
3402
3403class Window(Condition):
3404    arg_types = {
3405        "this": True,
3406        "partition_by": False,
3407        "order": False,
3408        "spec": False,
3409        "alias": False,
3410        "over": False,
3411        "first": False,
3412    }
3413
3414
3415class WindowSpec(Expression):
3416    arg_types = {
3417        "kind": False,
3418        "start": False,
3419        "start_side": False,
3420        "end": False,
3421        "end_side": False,
3422    }
3423
3424
3425class Where(Expression):
3426    pass
3427
3428
3429class Star(Expression):
3430    arg_types = {"except": False, "replace": False}
3431
3432    @property
3433    def name(self) -> str:
3434        return "*"
3435
3436    @property
3437    def output_name(self) -> str:
3438        return self.name
3439
3440
3441class Parameter(Condition):
3442    arg_types = {"this": True, "wrapped": False}
3443
3444
3445class SessionParameter(Condition):
3446    arg_types = {"this": True, "kind": False}
3447
3448
3449class Placeholder(Condition):
3450    arg_types = {"this": False, "kind": False}
3451
3452
3453class Null(Condition):
3454    arg_types: t.Dict[str, t.Any] = {}
3455
3456    @property
3457    def name(self) -> str:
3458        return "NULL"
3459
3460
3461class Boolean(Condition):
3462    pass
3463
3464
3465class DataTypeParam(Expression):
3466    arg_types = {"this": True, "expression": False}
3467
3468
3469class DataType(Expression):
3470    arg_types = {
3471        "this": True,
3472        "expressions": False,
3473        "nested": False,
3474        "values": False,
3475        "prefix": False,
3476        "kind": False,
3477    }
3478
3479    class Type(AutoName):
3480        ARRAY = auto()
3481        BIGDECIMAL = auto()
3482        BIGINT = auto()
3483        BIGSERIAL = auto()
3484        BINARY = auto()
3485        BIT = auto()
3486        BOOLEAN = auto()
3487        CHAR = auto()
3488        DATE = auto()
3489        DATEMULTIRANGE = auto()
3490        DATERANGE = auto()
3491        DATETIME = auto()
3492        DATETIME64 = auto()
3493        DECIMAL = auto()
3494        DOUBLE = auto()
3495        ENUM = auto()
3496        ENUM8 = auto()
3497        ENUM16 = auto()
3498        FIXEDSTRING = auto()
3499        FLOAT = auto()
3500        GEOGRAPHY = auto()
3501        GEOMETRY = auto()
3502        HLLSKETCH = auto()
3503        HSTORE = auto()
3504        IMAGE = auto()
3505        INET = auto()
3506        INT = auto()
3507        INT128 = auto()
3508        INT256 = auto()
3509        INT4MULTIRANGE = auto()
3510        INT4RANGE = auto()
3511        INT8MULTIRANGE = auto()
3512        INT8RANGE = auto()
3513        INTERVAL = auto()
3514        IPADDRESS = auto()
3515        IPPREFIX = auto()
3516        JSON = auto()
3517        JSONB = auto()
3518        LONGBLOB = auto()
3519        LONGTEXT = auto()
3520        LOWCARDINALITY = auto()
3521        MAP = auto()
3522        MEDIUMBLOB = auto()
3523        MEDIUMINT = auto()
3524        MEDIUMTEXT = auto()
3525        MONEY = auto()
3526        NCHAR = auto()
3527        NESTED = auto()
3528        NULL = auto()
3529        NULLABLE = auto()
3530        NUMMULTIRANGE = auto()
3531        NUMRANGE = auto()
3532        NVARCHAR = auto()
3533        OBJECT = auto()
3534        ROWVERSION = auto()
3535        SERIAL = auto()
3536        SET = auto()
3537        SMALLINT = auto()
3538        SMALLMONEY = auto()
3539        SMALLSERIAL = auto()
3540        STRUCT = auto()
3541        SUPER = auto()
3542        TEXT = auto()
3543        TINYBLOB = auto()
3544        TINYTEXT = auto()
3545        TIME = auto()
3546        TIMETZ = auto()
3547        TIMESTAMP = auto()
3548        TIMESTAMPLTZ = auto()
3549        TIMESTAMPTZ = auto()
3550        TINYINT = auto()
3551        TSMULTIRANGE = auto()
3552        TSRANGE = auto()
3553        TSTZMULTIRANGE = auto()
3554        TSTZRANGE = auto()
3555        UBIGINT = auto()
3556        UINT = auto()
3557        UINT128 = auto()
3558        UINT256 = auto()
3559        UMEDIUMINT = auto()
3560        UNIQUEIDENTIFIER = auto()
3561        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3562        USERDEFINED = "USER-DEFINED"
3563        USMALLINT = auto()
3564        UTINYINT = auto()
3565        UUID = auto()
3566        VARBINARY = auto()
3567        VARCHAR = auto()
3568        VARIANT = auto()
3569        XML = auto()
3570        YEAR = auto()
3571
3572    TEXT_TYPES = {
3573        Type.CHAR,
3574        Type.NCHAR,
3575        Type.VARCHAR,
3576        Type.NVARCHAR,
3577        Type.TEXT,
3578    }
3579
3580    INTEGER_TYPES = {
3581        Type.INT,
3582        Type.TINYINT,
3583        Type.SMALLINT,
3584        Type.BIGINT,
3585        Type.INT128,
3586        Type.INT256,
3587    }
3588
3589    FLOAT_TYPES = {
3590        Type.FLOAT,
3591        Type.DOUBLE,
3592    }
3593
3594    NUMERIC_TYPES = {
3595        *INTEGER_TYPES,
3596        *FLOAT_TYPES,
3597    }
3598
3599    TEMPORAL_TYPES = {
3600        Type.TIME,
3601        Type.TIMETZ,
3602        Type.TIMESTAMP,
3603        Type.TIMESTAMPTZ,
3604        Type.TIMESTAMPLTZ,
3605        Type.DATE,
3606        Type.DATETIME,
3607        Type.DATETIME64,
3608    }
3609
3610    @classmethod
3611    def build(
3612        cls,
3613        dtype: str | DataType | DataType.Type,
3614        dialect: DialectType = None,
3615        udt: bool = False,
3616        **kwargs,
3617    ) -> DataType:
3618        """
3619        Constructs a DataType object.
3620
3621        Args:
3622            dtype: the data type of interest.
3623            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3624            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3625                DataType, thus creating a user-defined type.
3626            kawrgs: additional arguments to pass in the constructor of DataType.
3627
3628        Returns:
3629            The constructed DataType object.
3630        """
3631        from sqlglot import parse_one
3632
3633        if isinstance(dtype, str):
3634            if dtype.upper() == "UNKNOWN":
3635                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3636
3637            try:
3638                data_type_exp = parse_one(dtype, read=dialect, into=DataType)
3639            except ParseError:
3640                if udt:
3641                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3642                raise
3643        elif isinstance(dtype, DataType.Type):
3644            data_type_exp = DataType(this=dtype)
3645        elif isinstance(dtype, DataType):
3646            return dtype
3647        else:
3648            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3649
3650        return DataType(**{**data_type_exp.args, **kwargs})
3651
3652    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3653        """
3654        Checks whether this DataType matches one of the provided data types. Nested types or precision
3655        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3656
3657        Args:
3658            dtypes: the data types to compare this DataType to.
3659
3660        Returns:
3661            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3662        """
3663        for dtype in dtypes:
3664            other = DataType.build(dtype, udt=True)
3665
3666            if (
3667                other.expressions
3668                or self.this == DataType.Type.USERDEFINED
3669                or other.this == DataType.Type.USERDEFINED
3670            ):
3671                matches = self == other
3672            else:
3673                matches = self.this == other.this
3674
3675            if matches:
3676                return True
3677        return False
3678
3679
3680# https://www.postgresql.org/docs/15/datatype-pseudo.html
3681class PseudoType(Expression):
3682    pass
3683
3684
3685# https://www.postgresql.org/docs/15/datatype-oid.html
3686class ObjectIdentifier(Expression):
3687    pass
3688
3689
3690# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3691class SubqueryPredicate(Predicate):
3692    pass
3693
3694
3695class All(SubqueryPredicate):
3696    pass
3697
3698
3699class Any(SubqueryPredicate):
3700    pass
3701
3702
3703class Exists(SubqueryPredicate):
3704    pass
3705
3706
3707# Commands to interact with the databases or engines. For most of the command
3708# expressions we parse whatever comes after the command's name as a string.
3709class Command(Expression):
3710    arg_types = {"this": True, "expression": False}
3711
3712
3713class Transaction(Expression):
3714    arg_types = {"this": False, "modes": False, "mark": False}
3715
3716
3717class Commit(Expression):
3718    arg_types = {"chain": False, "this": False, "durability": False}
3719
3720
3721class Rollback(Expression):
3722    arg_types = {"savepoint": False, "this": False}
3723
3724
3725class AlterTable(Expression):
3726    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3727
3728
3729class AddConstraint(Expression):
3730    arg_types = {"this": False, "expression": False, "enforced": False}
3731
3732
3733class DropPartition(Expression):
3734    arg_types = {"expressions": True, "exists": False}
3735
3736
3737# Binary expressions like (ADD a b)
3738class Binary(Condition):
3739    arg_types = {"this": True, "expression": True}
3740
3741    @property
3742    def left(self):
3743        return self.this
3744
3745    @property
3746    def right(self):
3747        return self.expression
3748
3749
3750class Add(Binary):
3751    pass
3752
3753
3754class Connector(Binary):
3755    pass
3756
3757
3758class And(Connector):
3759    pass
3760
3761
3762class Or(Connector):
3763    pass
3764
3765
3766class BitwiseAnd(Binary):
3767    pass
3768
3769
3770class BitwiseLeftShift(Binary):
3771    pass
3772
3773
3774class BitwiseOr(Binary):
3775    pass
3776
3777
3778class BitwiseRightShift(Binary):
3779    pass
3780
3781
3782class BitwiseXor(Binary):
3783    pass
3784
3785
3786class Div(Binary):
3787    pass
3788
3789
3790class Overlaps(Binary):
3791    pass
3792
3793
3794class Dot(Binary):
3795    @property
3796    def name(self) -> str:
3797        return self.expression.name
3798
3799    @property
3800    def output_name(self) -> str:
3801        return self.name
3802
3803    @classmethod
3804    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3805        """Build a Dot object with a sequence of expressions."""
3806        if len(expressions) < 2:
3807            raise ValueError(f"Dot requires >= 2 expressions.")
3808
3809        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3810
3811
3812class DPipe(Binary):
3813    pass
3814
3815
3816class SafeDPipe(DPipe):
3817    pass
3818
3819
3820class EQ(Binary, Predicate):
3821    pass
3822
3823
3824class NullSafeEQ(Binary, Predicate):
3825    pass
3826
3827
3828class NullSafeNEQ(Binary, Predicate):
3829    pass
3830
3831
3832class Distance(Binary):
3833    pass
3834
3835
3836class Escape(Binary):
3837    pass
3838
3839
3840class Glob(Binary, Predicate):
3841    pass
3842
3843
3844class GT(Binary, Predicate):
3845    pass
3846
3847
3848class GTE(Binary, Predicate):
3849    pass
3850
3851
3852class ILike(Binary, Predicate):
3853    pass
3854
3855
3856class ILikeAny(Binary, Predicate):
3857    pass
3858
3859
3860class IntDiv(Binary):
3861    pass
3862
3863
3864class Is(Binary, Predicate):
3865    pass
3866
3867
3868class Kwarg(Binary):
3869    """Kwarg in special functions like func(kwarg => y)."""
3870
3871
3872class Like(Binary, Predicate):
3873    pass
3874
3875
3876class LikeAny(Binary, Predicate):
3877    pass
3878
3879
3880class LT(Binary, Predicate):
3881    pass
3882
3883
3884class LTE(Binary, Predicate):
3885    pass
3886
3887
3888class Mod(Binary):
3889    pass
3890
3891
3892class Mul(Binary):
3893    pass
3894
3895
3896class NEQ(Binary, Predicate):
3897    pass
3898
3899
3900class SimilarTo(Binary, Predicate):
3901    pass
3902
3903
3904class Slice(Binary):
3905    arg_types = {"this": False, "expression": False}
3906
3907
3908class Sub(Binary):
3909    pass
3910
3911
3912class ArrayOverlaps(Binary):
3913    pass
3914
3915
3916# Unary Expressions
3917# (NOT a)
3918class Unary(Condition):
3919    pass
3920
3921
3922class BitwiseNot(Unary):
3923    pass
3924
3925
3926class Not(Unary):
3927    pass
3928
3929
3930class Paren(Unary):
3931    arg_types = {"this": True, "with": False}
3932
3933    @property
3934    def output_name(self) -> str:
3935        return self.this.name
3936
3937
3938class Neg(Unary):
3939    pass
3940
3941
3942class Alias(Expression):
3943    arg_types = {"this": True, "alias": False}
3944
3945    @property
3946    def output_name(self) -> str:
3947        return self.alias
3948
3949
3950class Aliases(Expression):
3951    arg_types = {"this": True, "expressions": True}
3952
3953    @property
3954    def aliases(self):
3955        return self.expressions
3956
3957
3958class AtTimeZone(Expression):
3959    arg_types = {"this": True, "zone": True}
3960
3961
3962class Between(Predicate):
3963    arg_types = {"this": True, "low": True, "high": True}
3964
3965
3966class Bracket(Condition):
3967    arg_types = {"this": True, "expressions": True}
3968
3969    @property
3970    def output_name(self) -> str:
3971        if len(self.expressions) == 1:
3972            return self.expressions[0].output_name
3973
3974        return super().output_name
3975
3976
3977class SafeBracket(Bracket):
3978    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
3979
3980
3981class Distinct(Expression):
3982    arg_types = {"expressions": False, "on": False}
3983
3984
3985class In(Predicate):
3986    arg_types = {
3987        "this": True,
3988        "expressions": False,
3989        "query": False,
3990        "unnest": False,
3991        "field": False,
3992        "is_global": False,
3993    }
3994
3995
3996class TimeUnit(Expression):
3997    """Automatically converts unit arg into a var."""
3998
3999    arg_types = {"unit": False}
4000
4001    def __init__(self, **args):
4002        unit = args.get("unit")
4003        if isinstance(unit, (Column, Literal)):
4004            args["unit"] = Var(this=unit.name)
4005        elif isinstance(unit, Week):
4006            unit.set("this", Var(this=unit.this.name))
4007
4008        super().__init__(**args)
4009
4010
4011# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4012# https://trino.io/docs/current/language/types.html#interval-day-to-second
4013# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4014class IntervalSpan(Expression):
4015    arg_types = {"this": True, "expression": True}
4016
4017
4018class Interval(TimeUnit):
4019    arg_types = {"this": False, "unit": False}
4020
4021    @property
4022    def unit(self) -> t.Optional[Var]:
4023        return self.args.get("unit")
4024
4025
4026class IgnoreNulls(Expression):
4027    pass
4028
4029
4030class RespectNulls(Expression):
4031    pass
4032
4033
4034# Functions
4035class Func(Condition):
4036    """
4037    The base class for all function expressions.
4038
4039    Attributes:
4040        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4041            treated as a variable length argument and the argument's value will be stored as a list.
4042        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4043            for this function expression. These values are used to map this node to a name during parsing
4044            as well as to provide the function's name during SQL string generation. By default the SQL
4045            name is set to the expression's class name transformed to snake case.
4046    """
4047
4048    is_var_len_args = False
4049
4050    @classmethod
4051    def from_arg_list(cls, args):
4052        if cls.is_var_len_args:
4053            all_arg_keys = list(cls.arg_types)
4054            # If this function supports variable length argument treat the last argument as such.
4055            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4056            num_non_var = len(non_var_len_arg_keys)
4057
4058            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4059            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4060        else:
4061            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4062
4063        return cls(**args_dict)
4064
4065    @classmethod
4066    def sql_names(cls):
4067        if cls is Func:
4068            raise NotImplementedError(
4069                "SQL name is only supported by concrete function implementations"
4070            )
4071        if "_sql_names" not in cls.__dict__:
4072            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4073        return cls._sql_names
4074
4075    @classmethod
4076    def sql_name(cls):
4077        return cls.sql_names()[0]
4078
4079    @classmethod
4080    def default_parser_mappings(cls):
4081        return {name: cls.from_arg_list for name in cls.sql_names()}
4082
4083
4084class AggFunc(Func):
4085    pass
4086
4087
4088class ParameterizedAgg(AggFunc):
4089    arg_types = {"this": True, "expressions": True, "params": True}
4090
4091
4092class Abs(Func):
4093    pass
4094
4095
4096# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4097class Transform(Func):
4098    arg_types = {"this": True, "expression": True}
4099
4100
4101class Anonymous(Func):
4102    arg_types = {"this": True, "expressions": False}
4103    is_var_len_args = True
4104
4105
4106# https://docs.snowflake.com/en/sql-reference/functions/hll
4107# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4108class Hll(AggFunc):
4109    arg_types = {"this": True, "expressions": False}
4110    is_var_len_args = True
4111
4112
4113class ApproxDistinct(AggFunc):
4114    arg_types = {"this": True, "accuracy": False}
4115    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4116
4117
4118class Array(Func):
4119    arg_types = {"expressions": False}
4120    is_var_len_args = True
4121
4122
4123# https://docs.snowflake.com/en/sql-reference/functions/to_char
4124class ToChar(Func):
4125    arg_types = {"this": True, "format": False}
4126
4127
4128class GenerateSeries(Func):
4129    arg_types = {"start": True, "end": True, "step": False}
4130
4131
4132class ArrayAgg(AggFunc):
4133    pass
4134
4135
4136class ArrayAll(Func):
4137    arg_types = {"this": True, "expression": True}
4138
4139
4140class ArrayAny(Func):
4141    arg_types = {"this": True, "expression": True}
4142
4143
4144class ArrayConcat(Func):
4145    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4146    arg_types = {"this": True, "expressions": False}
4147    is_var_len_args = True
4148
4149
4150class ArrayContains(Binary, Func):
4151    pass
4152
4153
4154class ArrayContained(Binary):
4155    pass
4156
4157
4158class ArrayFilter(Func):
4159    arg_types = {"this": True, "expression": True}
4160    _sql_names = ["FILTER", "ARRAY_FILTER"]
4161
4162
4163class ArrayJoin(Func):
4164    arg_types = {"this": True, "expression": True, "null": False}
4165
4166
4167class ArraySize(Func):
4168    arg_types = {"this": True, "expression": False}
4169
4170
4171class ArraySort(Func):
4172    arg_types = {"this": True, "expression": False}
4173
4174
4175class ArraySum(Func):
4176    pass
4177
4178
4179class ArrayUnionAgg(AggFunc):
4180    pass
4181
4182
4183class Avg(AggFunc):
4184    pass
4185
4186
4187class AnyValue(AggFunc):
4188    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4189
4190
4191class First(Func):
4192    arg_types = {"this": True, "ignore_nulls": False}
4193
4194
4195class Last(Func):
4196    arg_types = {"this": True, "ignore_nulls": False}
4197
4198
4199class Case(Func):
4200    arg_types = {"this": False, "ifs": True, "default": False}
4201
4202    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4203        instance = maybe_copy(self, copy)
4204        instance.append(
4205            "ifs",
4206            If(
4207                this=maybe_parse(condition, copy=copy, **opts),
4208                true=maybe_parse(then, copy=copy, **opts),
4209            ),
4210        )
4211        return instance
4212
4213    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4214        instance = maybe_copy(self, copy)
4215        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4216        return instance
4217
4218
4219class Cast(Func):
4220    arg_types = {"this": True, "to": True, "format": False}
4221
4222    @property
4223    def name(self) -> str:
4224        return self.this.name
4225
4226    @property
4227    def to(self) -> DataType:
4228        return self.args["to"]
4229
4230    @property
4231    def output_name(self) -> str:
4232        return self.name
4233
4234    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4235        """
4236        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4237        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4238        array<int> != array<float>.
4239
4240        Args:
4241            dtypes: the data types to compare this Cast's DataType to.
4242
4243        Returns:
4244            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4245        """
4246        return self.to.is_type(*dtypes)
4247
4248
4249class TryCast(Cast):
4250    pass
4251
4252
4253class CastToStrType(Func):
4254    arg_types = {"this": True, "to": True}
4255
4256
4257class Collate(Binary):
4258    pass
4259
4260
4261class Ceil(Func):
4262    arg_types = {"this": True, "decimals": False}
4263    _sql_names = ["CEIL", "CEILING"]
4264
4265
4266class Coalesce(Func):
4267    arg_types = {"this": True, "expressions": False}
4268    is_var_len_args = True
4269    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4270
4271
4272class Concat(Func):
4273    arg_types = {"expressions": True}
4274    is_var_len_args = True
4275
4276
4277class SafeConcat(Concat):
4278    pass
4279
4280
4281class ConcatWs(Concat):
4282    _sql_names = ["CONCAT_WS"]
4283
4284
4285class Count(AggFunc):
4286    arg_types = {"this": False, "expressions": False}
4287    is_var_len_args = True
4288
4289
4290class CountIf(AggFunc):
4291    pass
4292
4293
4294class CurrentDate(Func):
4295    arg_types = {"this": False}
4296
4297
4298class CurrentDatetime(Func):
4299    arg_types = {"this": False}
4300
4301
4302class CurrentTime(Func):
4303    arg_types = {"this": False}
4304
4305
4306class CurrentTimestamp(Func):
4307    arg_types = {"this": False}
4308
4309
4310class CurrentUser(Func):
4311    arg_types = {"this": False}
4312
4313
4314class DateAdd(Func, TimeUnit):
4315    arg_types = {"this": True, "expression": True, "unit": False}
4316
4317
4318class DateSub(Func, TimeUnit):
4319    arg_types = {"this": True, "expression": True, "unit": False}
4320
4321
4322class DateDiff(Func, TimeUnit):
4323    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4324    arg_types = {"this": True, "expression": True, "unit": False}
4325
4326
4327class DateTrunc(Func):
4328    arg_types = {"unit": True, "this": True, "zone": False}
4329
4330
4331class DatetimeAdd(Func, TimeUnit):
4332    arg_types = {"this": True, "expression": True, "unit": False}
4333
4334
4335class DatetimeSub(Func, TimeUnit):
4336    arg_types = {"this": True, "expression": True, "unit": False}
4337
4338
4339class DatetimeDiff(Func, TimeUnit):
4340    arg_types = {"this": True, "expression": True, "unit": False}
4341
4342
4343class DatetimeTrunc(Func, TimeUnit):
4344    arg_types = {"this": True, "unit": True, "zone": False}
4345
4346
4347class DayOfWeek(Func):
4348    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4349
4350
4351class DayOfMonth(Func):
4352    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4353
4354
4355class DayOfYear(Func):
4356    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4357
4358
4359class WeekOfYear(Func):
4360    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4361
4362
4363class MonthsBetween(Func):
4364    arg_types = {"this": True, "expression": True, "roundoff": False}
4365
4366
4367class LastDateOfMonth(Func):
4368    pass
4369
4370
4371class Extract(Func):
4372    arg_types = {"this": True, "expression": True}
4373
4374
4375class Timestamp(Func):
4376    arg_types = {"this": False, "expression": False}
4377
4378
4379class TimestampAdd(Func, TimeUnit):
4380    arg_types = {"this": True, "expression": True, "unit": False}
4381
4382
4383class TimestampSub(Func, TimeUnit):
4384    arg_types = {"this": True, "expression": True, "unit": False}
4385
4386
4387class TimestampDiff(Func, TimeUnit):
4388    arg_types = {"this": True, "expression": True, "unit": False}
4389
4390
4391class TimestampTrunc(Func, TimeUnit):
4392    arg_types = {"this": True, "unit": True, "zone": False}
4393
4394
4395class TimeAdd(Func, TimeUnit):
4396    arg_types = {"this": True, "expression": True, "unit": False}
4397
4398
4399class TimeSub(Func, TimeUnit):
4400    arg_types = {"this": True, "expression": True, "unit": False}
4401
4402
4403class TimeDiff(Func, TimeUnit):
4404    arg_types = {"this": True, "expression": True, "unit": False}
4405
4406
4407class TimeTrunc(Func, TimeUnit):
4408    arg_types = {"this": True, "unit": True, "zone": False}
4409
4410
4411class DateFromParts(Func):
4412    _sql_names = ["DATEFROMPARTS"]
4413    arg_types = {"year": True, "month": True, "day": True}
4414
4415
4416class DateStrToDate(Func):
4417    pass
4418
4419
4420class DateToDateStr(Func):
4421    pass
4422
4423
4424class DateToDi(Func):
4425    pass
4426
4427
4428# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4429class Date(Func):
4430    arg_types = {"this": True, "zone": False}
4431
4432
4433class Day(Func):
4434    pass
4435
4436
4437class Decode(Func):
4438    arg_types = {"this": True, "charset": True, "replace": False}
4439
4440
4441class DiToDate(Func):
4442    pass
4443
4444
4445class Encode(Func):
4446    arg_types = {"this": True, "charset": True}
4447
4448
4449class Exp(Func):
4450    pass
4451
4452
4453class Explode(Func):
4454    pass
4455
4456
4457class Floor(Func):
4458    arg_types = {"this": True, "decimals": False}
4459
4460
4461class FromBase64(Func):
4462    pass
4463
4464
4465class ToBase64(Func):
4466    pass
4467
4468
4469class Greatest(Func):
4470    arg_types = {"this": True, "expressions": False}
4471    is_var_len_args = True
4472
4473
4474class GroupConcat(AggFunc):
4475    arg_types = {"this": True, "separator": False}
4476
4477
4478class Hex(Func):
4479    pass
4480
4481
4482class Xor(Connector, Func):
4483    arg_types = {"this": False, "expression": False, "expressions": False}
4484
4485
4486class If(Func):
4487    arg_types = {"this": True, "true": True, "false": False}
4488
4489
4490class Initcap(Func):
4491    arg_types = {"this": True, "expression": False}
4492
4493
4494class IsNan(Func):
4495    _sql_names = ["IS_NAN", "ISNAN"]
4496
4497
4498class FormatJson(Expression):
4499    pass
4500
4501
4502class JSONKeyValue(Expression):
4503    arg_types = {"this": True, "expression": True}
4504
4505
4506class JSONObject(Func):
4507    arg_types = {
4508        "expressions": False,
4509        "null_handling": False,
4510        "unique_keys": False,
4511        "return_type": False,
4512        "encoding": False,
4513    }
4514
4515
4516# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4517class JSONArray(Func):
4518    arg_types = {
4519        "expressions": True,
4520        "null_handling": False,
4521        "return_type": False,
4522        "strict": False,
4523    }
4524
4525
4526# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4527class JSONArrayAgg(Func):
4528    arg_types = {
4529        "this": True,
4530        "order": False,
4531        "null_handling": False,
4532        "return_type": False,
4533        "strict": False,
4534    }
4535
4536
4537# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4538# Note: parsing of JSON column definitions is currently incomplete.
4539class JSONColumnDef(Expression):
4540    arg_types = {"this": True, "kind": False, "path": False}
4541
4542
4543# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4544class JSONTable(Func):
4545    arg_types = {
4546        "this": True,
4547        "expressions": True,
4548        "path": False,
4549        "error_handling": False,
4550        "empty_handling": False,
4551    }
4552
4553
4554class OpenJSONColumnDef(Expression):
4555    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4556
4557
4558class OpenJSON(Func):
4559    arg_types = {"this": True, "path": False, "expressions": False}
4560
4561
4562class JSONBContains(Binary):
4563    _sql_names = ["JSONB_CONTAINS"]
4564
4565
4566class JSONExtract(Binary, Func):
4567    _sql_names = ["JSON_EXTRACT"]
4568
4569
4570class JSONExtractScalar(JSONExtract):
4571    _sql_names = ["JSON_EXTRACT_SCALAR"]
4572
4573
4574class JSONBExtract(JSONExtract):
4575    _sql_names = ["JSONB_EXTRACT"]
4576
4577
4578class JSONBExtractScalar(JSONExtract):
4579    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4580
4581
4582class JSONFormat(Func):
4583    arg_types = {"this": False, "options": False}
4584    _sql_names = ["JSON_FORMAT"]
4585
4586
4587# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4588class JSONArrayContains(Binary, Predicate, Func):
4589    _sql_names = ["JSON_ARRAY_CONTAINS"]
4590
4591
4592class ParseJSON(Func):
4593    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4594    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4595
4596
4597class Least(Func):
4598    arg_types = {"this": True, "expressions": False}
4599    is_var_len_args = True
4600
4601
4602class Left(Func):
4603    arg_types = {"this": True, "expression": True}
4604
4605
4606class Right(Func):
4607    arg_types = {"this": True, "expression": True}
4608
4609
4610class Length(Func):
4611    _sql_names = ["LENGTH", "LEN"]
4612
4613
4614class Levenshtein(Func):
4615    arg_types = {
4616        "this": True,
4617        "expression": False,
4618        "ins_cost": False,
4619        "del_cost": False,
4620        "sub_cost": False,
4621    }
4622
4623
4624class Ln(Func):
4625    pass
4626
4627
4628class Log(Func):
4629    arg_types = {"this": True, "expression": False}
4630
4631
4632class Log2(Func):
4633    pass
4634
4635
4636class Log10(Func):
4637    pass
4638
4639
4640class LogicalOr(AggFunc):
4641    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4642
4643
4644class LogicalAnd(AggFunc):
4645    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4646
4647
4648class Lower(Func):
4649    _sql_names = ["LOWER", "LCASE"]
4650
4651
4652class Map(Func):
4653    arg_types = {"keys": False, "values": False}
4654
4655
4656class MapFromEntries(Func):
4657    pass
4658
4659
4660class StarMap(Func):
4661    pass
4662
4663
4664class VarMap(Func):
4665    arg_types = {"keys": True, "values": True}
4666    is_var_len_args = True
4667
4668    @property
4669    def keys(self) -> t.List[Expression]:
4670        return self.args["keys"].expressions
4671
4672    @property
4673    def values(self) -> t.List[Expression]:
4674        return self.args["values"].expressions
4675
4676
4677# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4678class MatchAgainst(Func):
4679    arg_types = {"this": True, "expressions": True, "modifier": False}
4680
4681
4682class Max(AggFunc):
4683    arg_types = {"this": True, "expressions": False}
4684    is_var_len_args = True
4685
4686
4687class MD5(Func):
4688    _sql_names = ["MD5"]
4689
4690
4691# Represents the variant of the MD5 function that returns a binary value
4692class MD5Digest(Func):
4693    _sql_names = ["MD5_DIGEST"]
4694
4695
4696class Min(AggFunc):
4697    arg_types = {"this": True, "expressions": False}
4698    is_var_len_args = True
4699
4700
4701class Month(Func):
4702    pass
4703
4704
4705class Nvl2(Func):
4706    arg_types = {"this": True, "true": True, "false": False}
4707
4708
4709class Posexplode(Func):
4710    pass
4711
4712
4713class Pow(Binary, Func):
4714    _sql_names = ["POWER", "POW"]
4715
4716
4717class PercentileCont(AggFunc):
4718    arg_types = {"this": True, "expression": False}
4719
4720
4721class PercentileDisc(AggFunc):
4722    arg_types = {"this": True, "expression": False}
4723
4724
4725class Quantile(AggFunc):
4726    arg_types = {"this": True, "quantile": True}
4727
4728
4729class ApproxQuantile(Quantile):
4730    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4731
4732
4733class RangeN(Func):
4734    arg_types = {"this": True, "expressions": True, "each": False}
4735
4736
4737class ReadCSV(Func):
4738    _sql_names = ["READ_CSV"]
4739    is_var_len_args = True
4740    arg_types = {"this": True, "expressions": False}
4741
4742
4743class Reduce(Func):
4744    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4745
4746
4747class RegexpExtract(Func):
4748    arg_types = {
4749        "this": True,
4750        "expression": True,
4751        "position": False,
4752        "occurrence": False,
4753        "parameters": False,
4754        "group": False,
4755    }
4756
4757
4758class RegexpReplace(Func):
4759    arg_types = {
4760        "this": True,
4761        "expression": True,
4762        "replacement": True,
4763        "position": False,
4764        "occurrence": False,
4765        "parameters": False,
4766    }
4767
4768
4769class RegexpLike(Binary, Func):
4770    arg_types = {"this": True, "expression": True, "flag": False}
4771
4772
4773class RegexpILike(Func):
4774    arg_types = {"this": True, "expression": True, "flag": False}
4775
4776
4777# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4778# limit is the number of times a pattern is applied
4779class RegexpSplit(Func):
4780    arg_types = {"this": True, "expression": True, "limit": False}
4781
4782
4783class Repeat(Func):
4784    arg_types = {"this": True, "times": True}
4785
4786
4787class Round(Func):
4788    arg_types = {"this": True, "decimals": False}
4789
4790
4791class RowNumber(Func):
4792    arg_types: t.Dict[str, t.Any] = {}
4793
4794
4795class SafeDivide(Func):
4796    arg_types = {"this": True, "expression": True}
4797
4798
4799class SetAgg(AggFunc):
4800    pass
4801
4802
4803class SHA(Func):
4804    _sql_names = ["SHA", "SHA1"]
4805
4806
4807class SHA2(Func):
4808    _sql_names = ["SHA2"]
4809    arg_types = {"this": True, "length": False}
4810
4811
4812class SortArray(Func):
4813    arg_types = {"this": True, "asc": False}
4814
4815
4816class Split(Func):
4817    arg_types = {"this": True, "expression": True, "limit": False}
4818
4819
4820# Start may be omitted in the case of postgres
4821# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
4822class Substring(Func):
4823    arg_types = {"this": True, "start": False, "length": False}
4824
4825
4826class StandardHash(Func):
4827    arg_types = {"this": True, "expression": False}
4828
4829
4830class StartsWith(Func):
4831    _sql_names = ["STARTS_WITH", "STARTSWITH"]
4832    arg_types = {"this": True, "expression": True}
4833
4834
4835class StrPosition(Func):
4836    arg_types = {
4837        "this": True,
4838        "substr": True,
4839        "position": False,
4840        "instance": False,
4841    }
4842
4843
4844class StrToDate(Func):
4845    arg_types = {"this": True, "format": True}
4846
4847
4848class StrToTime(Func):
4849    arg_types = {"this": True, "format": True, "zone": False}
4850
4851
4852# Spark allows unix_timestamp()
4853# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
4854class StrToUnix(Func):
4855    arg_types = {"this": False, "format": False}
4856
4857
4858# https://prestodb.io/docs/current/functions/string.html
4859# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
4860class StrToMap(Func):
4861    arg_types = {
4862        "this": True,
4863        "pair_delim": False,
4864        "key_value_delim": False,
4865        "duplicate_resolution_callback": False,
4866    }
4867
4868
4869class NumberToStr(Func):
4870    arg_types = {"this": True, "format": True, "culture": False}
4871
4872
4873class FromBase(Func):
4874    arg_types = {"this": True, "expression": True}
4875
4876
4877class Struct(Func):
4878    arg_types = {"expressions": True}
4879    is_var_len_args = True
4880
4881
4882class StructExtract(Func):
4883    arg_types = {"this": True, "expression": True}
4884
4885
4886# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
4887# https://docs.snowflake.com/en/sql-reference/functions/insert
4888class Stuff(Func):
4889    _sql_names = ["STUFF", "INSERT"]
4890    arg_types = {"this": True, "start": True, "length": True, "expression": True}
4891
4892
4893class Sum(AggFunc):
4894    pass
4895
4896
4897class Sqrt(Func):
4898    pass
4899
4900
4901class Stddev(AggFunc):
4902    pass
4903
4904
4905class StddevPop(AggFunc):
4906    pass
4907
4908
4909class StddevSamp(AggFunc):
4910    pass
4911
4912
4913class TimeToStr(Func):
4914    arg_types = {"this": True, "format": True, "culture": False}
4915
4916
4917class TimeToTimeStr(Func):
4918    pass
4919
4920
4921class TimeToUnix(Func):
4922    pass
4923
4924
4925class TimeStrToDate(Func):
4926    pass
4927
4928
4929class TimeStrToTime(Func):
4930    pass
4931
4932
4933class TimeStrToUnix(Func):
4934    pass
4935
4936
4937class Trim(Func):
4938    arg_types = {
4939        "this": True,
4940        "expression": False,
4941        "position": False,
4942        "collation": False,
4943    }
4944
4945
4946class TsOrDsAdd(Func, TimeUnit):
4947    arg_types = {"this": True, "expression": True, "unit": False}
4948
4949
4950class TsOrDsToDateStr(Func):
4951    pass
4952
4953
4954class TsOrDsToDate(Func):
4955    arg_types = {"this": True, "format": False}
4956
4957
4958class TsOrDiToDi(Func):
4959    pass
4960
4961
4962class Unhex(Func):
4963    pass
4964
4965
4966class UnixToStr(Func):
4967    arg_types = {"this": True, "format": False}
4968
4969
4970# https://prestodb.io/docs/current/functions/datetime.html
4971# presto has weird zone/hours/minutes
4972class UnixToTime(Func):
4973    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
4974
4975    SECONDS = Literal.string("seconds")
4976    MILLIS = Literal.string("millis")
4977    MICROS = Literal.string("micros")
4978
4979
4980class UnixToTimeStr(Func):
4981    pass
4982
4983
4984class Upper(Func):
4985    _sql_names = ["UPPER", "UCASE"]
4986
4987
4988class Variance(AggFunc):
4989    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
4990
4991
4992class VariancePop(AggFunc):
4993    _sql_names = ["VARIANCE_POP", "VAR_POP"]
4994
4995
4996class Week(Func):
4997    arg_types = {"this": True, "mode": False}
4998
4999
5000class XMLTable(Func):
5001    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5002
5003
5004class Year(Func):
5005    pass
5006
5007
5008class Use(Expression):
5009    arg_types = {"this": True, "kind": False}
5010
5011
5012class Merge(Expression):
5013    arg_types = {"this": True, "using": True, "on": True, "expressions": True}
5014
5015
5016class When(Func):
5017    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5018
5019
5020# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5021# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5022class NextValueFor(Func):
5023    arg_types = {"this": True, "order": False}
5024
5025
5026def _norm_arg(arg):
5027    return arg.lower() if type(arg) is str else arg
5028
5029
5030ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5031
5032
5033# Helpers
5034@t.overload
5035def maybe_parse(
5036    sql_or_expression: ExpOrStr,
5037    *,
5038    into: t.Type[E],
5039    dialect: DialectType = None,
5040    prefix: t.Optional[str] = None,
5041    copy: bool = False,
5042    **opts,
5043) -> E:
5044    ...
5045
5046
5047@t.overload
5048def maybe_parse(
5049    sql_or_expression: str | E,
5050    *,
5051    into: t.Optional[IntoType] = None,
5052    dialect: DialectType = None,
5053    prefix: t.Optional[str] = None,
5054    copy: bool = False,
5055    **opts,
5056) -> E:
5057    ...
5058
5059
5060def maybe_parse(
5061    sql_or_expression: ExpOrStr,
5062    *,
5063    into: t.Optional[IntoType] = None,
5064    dialect: DialectType = None,
5065    prefix: t.Optional[str] = None,
5066    copy: bool = False,
5067    **opts,
5068) -> Expression:
5069    """Gracefully handle a possible string or expression.
5070
5071    Example:
5072        >>> maybe_parse("1")
5073        (LITERAL this: 1, is_string: False)
5074        >>> maybe_parse(to_identifier("x"))
5075        (IDENTIFIER this: x, quoted: False)
5076
5077    Args:
5078        sql_or_expression: the SQL code string or an expression
5079        into: the SQLGlot Expression to parse into
5080        dialect: the dialect used to parse the input expressions (in the case that an
5081            input expression is a SQL string).
5082        prefix: a string to prefix the sql with before it gets parsed
5083            (automatically includes a space)
5084        copy: whether or not to copy the expression.
5085        **opts: other options to use to parse the input expressions (again, in the case
5086            that an input expression is a SQL string).
5087
5088    Returns:
5089        Expression: the parsed or given expression.
5090    """
5091    if isinstance(sql_or_expression, Expression):
5092        if copy:
5093            return sql_or_expression.copy()
5094        return sql_or_expression
5095
5096    if sql_or_expression is None:
5097        raise ParseError(f"SQL cannot be None")
5098
5099    import sqlglot
5100
5101    sql = str(sql_or_expression)
5102    if prefix:
5103        sql = f"{prefix} {sql}"
5104
5105    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5106
5107
5108@t.overload
5109def maybe_copy(instance: None, copy: bool = True) -> None:
5110    ...
5111
5112
5113@t.overload
5114def maybe_copy(instance: E, copy: bool = True) -> E:
5115    ...
5116
5117
5118def maybe_copy(instance, copy=True):
5119    return instance.copy() if copy and instance else instance
5120
5121
5122def _is_wrong_expression(expression, into):
5123    return isinstance(expression, Expression) and not isinstance(expression, into)
5124
5125
5126def _apply_builder(
5127    expression,
5128    instance,
5129    arg,
5130    copy=True,
5131    prefix=None,
5132    into=None,
5133    dialect=None,
5134    **opts,
5135):
5136    if _is_wrong_expression(expression, into):
5137        expression = into(this=expression)
5138    instance = maybe_copy(instance, copy)
5139    expression = maybe_parse(
5140        sql_or_expression=expression,
5141        prefix=prefix,
5142        into=into,
5143        dialect=dialect,
5144        **opts,
5145    )
5146    instance.set(arg, expression)
5147    return instance
5148
5149
5150def _apply_child_list_builder(
5151    *expressions,
5152    instance,
5153    arg,
5154    append=True,
5155    copy=True,
5156    prefix=None,
5157    into=None,
5158    dialect=None,
5159    properties=None,
5160    **opts,
5161):
5162    instance = maybe_copy(instance, copy)
5163    parsed = []
5164    for expression in expressions:
5165        if expression is not None:
5166            if _is_wrong_expression(expression, into):
5167                expression = into(expressions=[expression])
5168
5169            expression = maybe_parse(
5170                expression,
5171                into=into,
5172                dialect=dialect,
5173                prefix=prefix,
5174                **opts,
5175            )
5176            parsed.extend(expression.expressions)
5177
5178    existing = instance.args.get(arg)
5179    if append and existing:
5180        parsed = existing.expressions + parsed
5181
5182    child = into(expressions=parsed)
5183    for k, v in (properties or {}).items():
5184        child.set(k, v)
5185    instance.set(arg, child)
5186
5187    return instance
5188
5189
5190def _apply_list_builder(
5191    *expressions,
5192    instance,
5193    arg,
5194    append=True,
5195    copy=True,
5196    prefix=None,
5197    into=None,
5198    dialect=None,
5199    **opts,
5200):
5201    inst = maybe_copy(instance, copy)
5202
5203    expressions = [
5204        maybe_parse(
5205            sql_or_expression=expression,
5206            into=into,
5207            prefix=prefix,
5208            dialect=dialect,
5209            **opts,
5210        )
5211        for expression in expressions
5212        if expression is not None
5213    ]
5214
5215    existing_expressions = inst.args.get(arg)
5216    if append and existing_expressions:
5217        expressions = existing_expressions + expressions
5218
5219    inst.set(arg, expressions)
5220    return inst
5221
5222
5223def _apply_conjunction_builder(
5224    *expressions,
5225    instance,
5226    arg,
5227    into=None,
5228    append=True,
5229    copy=True,
5230    dialect=None,
5231    **opts,
5232):
5233    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5234    if not expressions:
5235        return instance
5236
5237    inst = maybe_copy(instance, copy)
5238
5239    existing = inst.args.get(arg)
5240    if append and existing is not None:
5241        expressions = [existing.this if into else existing] + list(expressions)
5242
5243    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5244
5245    inst.set(arg, into(this=node) if into else node)
5246    return inst
5247
5248
5249def _apply_cte_builder(
5250    instance: E,
5251    alias: ExpOrStr,
5252    as_: ExpOrStr,
5253    recursive: t.Optional[bool] = None,
5254    append: bool = True,
5255    dialect: DialectType = None,
5256    copy: bool = True,
5257    **opts,
5258) -> E:
5259    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5260    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5261    cte = CTE(this=as_expression, alias=alias_expression)
5262    return _apply_child_list_builder(
5263        cte,
5264        instance=instance,
5265        arg="with",
5266        append=append,
5267        copy=copy,
5268        into=With,
5269        properties={"recursive": recursive or False},
5270    )
5271
5272
5273def _combine(
5274    expressions: t.Sequence[t.Optional[ExpOrStr]],
5275    operator: t.Type[Connector],
5276    dialect: DialectType = None,
5277    copy: bool = True,
5278    **opts,
5279) -> Expression:
5280    conditions = [
5281        condition(expression, dialect=dialect, copy=copy, **opts)
5282        for expression in expressions
5283        if expression is not None
5284    ]
5285
5286    this, *rest = conditions
5287    if rest:
5288        this = _wrap(this, Connector)
5289    for expression in rest:
5290        this = operator(this=this, expression=_wrap(expression, Connector))
5291
5292    return this
5293
5294
5295def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5296    return Paren(this=expression) if isinstance(expression, kind) else expression
5297
5298
5299def union(
5300    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5301) -> Union:
5302    """
5303    Initializes a syntax tree from one UNION expression.
5304
5305    Example:
5306        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5307        'SELECT * FROM foo UNION SELECT * FROM bla'
5308
5309    Args:
5310        left: the SQL code string corresponding to the left-hand side.
5311            If an `Expression` instance is passed, it will be used as-is.
5312        right: the SQL code string corresponding to the right-hand side.
5313            If an `Expression` instance is passed, it will be used as-is.
5314        distinct: set the DISTINCT flag if and only if this is true.
5315        dialect: the dialect used to parse the input expression.
5316        opts: other options to use to parse the input expressions.
5317
5318    Returns:
5319        The new Union instance.
5320    """
5321    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5322    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5323
5324    return Union(this=left, expression=right, distinct=distinct)
5325
5326
5327def intersect(
5328    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5329) -> Intersect:
5330    """
5331    Initializes a syntax tree from one INTERSECT expression.
5332
5333    Example:
5334        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5335        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5336
5337    Args:
5338        left: the SQL code string corresponding to the left-hand side.
5339            If an `Expression` instance is passed, it will be used as-is.
5340        right: the SQL code string corresponding to the right-hand side.
5341            If an `Expression` instance is passed, it will be used as-is.
5342        distinct: set the DISTINCT flag if and only if this is true.
5343        dialect: the dialect used to parse the input expression.
5344        opts: other options to use to parse the input expressions.
5345
5346    Returns:
5347        The new Intersect instance.
5348    """
5349    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5350    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5351
5352    return Intersect(this=left, expression=right, distinct=distinct)
5353
5354
5355def except_(
5356    left: ExpOrStr, right: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
5357) -> Except:
5358    """
5359    Initializes a syntax tree from one EXCEPT expression.
5360
5361    Example:
5362        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5363        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5364
5365    Args:
5366        left: the SQL code string corresponding to the left-hand side.
5367            If an `Expression` instance is passed, it will be used as-is.
5368        right: the SQL code string corresponding to the right-hand side.
5369            If an `Expression` instance is passed, it will be used as-is.
5370        distinct: set the DISTINCT flag if and only if this is true.
5371        dialect: the dialect used to parse the input expression.
5372        opts: other options to use to parse the input expressions.
5373
5374    Returns:
5375        The new Except instance.
5376    """
5377    left = maybe_parse(sql_or_expression=left, dialect=dialect, **opts)
5378    right = maybe_parse(sql_or_expression=right, dialect=dialect, **opts)
5379
5380    return Except(this=left, expression=right, distinct=distinct)
5381
5382
5383def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5384    """
5385    Initializes a syntax tree from one or multiple SELECT expressions.
5386
5387    Example:
5388        >>> select("col1", "col2").from_("tbl").sql()
5389        'SELECT col1, col2 FROM tbl'
5390
5391    Args:
5392        *expressions: the SQL code string to parse as the expressions of a
5393            SELECT statement. If an Expression instance is passed, this is used as-is.
5394        dialect: the dialect used to parse the input expressions (in the case that an
5395            input expression is a SQL string).
5396        **opts: other options to use to parse the input expressions (again, in the case
5397            that an input expression is a SQL string).
5398
5399    Returns:
5400        Select: the syntax tree for the SELECT statement.
5401    """
5402    return Select().select(*expressions, dialect=dialect, **opts)
5403
5404
5405def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5406    """
5407    Initializes a syntax tree from a FROM expression.
5408
5409    Example:
5410        >>> from_("tbl").select("col1", "col2").sql()
5411        'SELECT col1, col2 FROM tbl'
5412
5413    Args:
5414        *expression: the SQL code string to parse as the FROM expressions of a
5415            SELECT statement. If an Expression instance is passed, this is used as-is.
5416        dialect: the dialect used to parse the input expression (in the case that the
5417            input expression is a SQL string).
5418        **opts: other options to use to parse the input expressions (again, in the case
5419            that the input expression is a SQL string).
5420
5421    Returns:
5422        Select: the syntax tree for the SELECT statement.
5423    """
5424    return Select().from_(expression, dialect=dialect, **opts)
5425
5426
5427def update(
5428    table: str | Table,
5429    properties: dict,
5430    where: t.Optional[ExpOrStr] = None,
5431    from_: t.Optional[ExpOrStr] = None,
5432    dialect: DialectType = None,
5433    **opts,
5434) -> Update:
5435    """
5436    Creates an update statement.
5437
5438    Example:
5439        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5440        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5441
5442    Args:
5443        *properties: dictionary of properties to set which are
5444            auto converted to sql objects eg None -> NULL
5445        where: sql conditional parsed into a WHERE statement
5446        from_: sql statement parsed into a FROM statement
5447        dialect: the dialect used to parse the input expressions.
5448        **opts: other options to use to parse the input expressions.
5449
5450    Returns:
5451        Update: the syntax tree for the UPDATE statement.
5452    """
5453    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5454    update_expr.set(
5455        "expressions",
5456        [
5457            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5458            for k, v in properties.items()
5459        ],
5460    )
5461    if from_:
5462        update_expr.set(
5463            "from",
5464            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5465        )
5466    if isinstance(where, Condition):
5467        where = Where(this=where)
5468    if where:
5469        update_expr.set(
5470            "where",
5471            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5472        )
5473    return update_expr
5474
5475
5476def delete(
5477    table: ExpOrStr,
5478    where: t.Optional[ExpOrStr] = None,
5479    returning: t.Optional[ExpOrStr] = None,
5480    dialect: DialectType = None,
5481    **opts,
5482) -> Delete:
5483    """
5484    Builds a delete statement.
5485
5486    Example:
5487        >>> delete("my_table", where="id > 1").sql()
5488        'DELETE FROM my_table WHERE id > 1'
5489
5490    Args:
5491        where: sql conditional parsed into a WHERE statement
5492        returning: sql conditional parsed into a RETURNING statement
5493        dialect: the dialect used to parse the input expressions.
5494        **opts: other options to use to parse the input expressions.
5495
5496    Returns:
5497        Delete: the syntax tree for the DELETE statement.
5498    """
5499    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5500    if where:
5501        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5502    if returning:
5503        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5504    return delete_expr
5505
5506
5507def insert(
5508    expression: ExpOrStr,
5509    into: ExpOrStr,
5510    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5511    overwrite: t.Optional[bool] = None,
5512    dialect: DialectType = None,
5513    copy: bool = True,
5514    **opts,
5515) -> Insert:
5516    """
5517    Builds an INSERT statement.
5518
5519    Example:
5520        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5521        'INSERT INTO tbl VALUES (1, 2, 3)'
5522
5523    Args:
5524        expression: the sql string or expression of the INSERT statement
5525        into: the tbl to insert data to.
5526        columns: optionally the table's column names.
5527        overwrite: whether to INSERT OVERWRITE or not.
5528        dialect: the dialect used to parse the input expressions.
5529        copy: whether or not to copy the expression.
5530        **opts: other options to use to parse the input expressions.
5531
5532    Returns:
5533        Insert: the syntax tree for the INSERT statement.
5534    """
5535    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5536    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5537
5538    if columns:
5539        this = _apply_list_builder(
5540            *columns,
5541            instance=Schema(this=this),
5542            arg="expressions",
5543            into=Identifier,
5544            copy=False,
5545            dialect=dialect,
5546            **opts,
5547        )
5548
5549    return Insert(this=this, expression=expr, overwrite=overwrite)
5550
5551
5552def condition(
5553    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5554) -> Condition:
5555    """
5556    Initialize a logical condition expression.
5557
5558    Example:
5559        >>> condition("x=1").sql()
5560        'x = 1'
5561
5562        This is helpful for composing larger logical syntax trees:
5563        >>> where = condition("x=1")
5564        >>> where = where.and_("y=1")
5565        >>> Select().from_("tbl").select("*").where(where).sql()
5566        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5567
5568    Args:
5569        *expression: the SQL code string to parse.
5570            If an Expression instance is passed, this is used as-is.
5571        dialect: the dialect used to parse the input expression (in the case that the
5572            input expression is a SQL string).
5573        copy: Whether or not to copy `expression` (only applies to expressions).
5574        **opts: other options to use to parse the input expressions (again, in the case
5575            that the input expression is a SQL string).
5576
5577    Returns:
5578        The new Condition instance
5579    """
5580    return maybe_parse(
5581        expression,
5582        into=Condition,
5583        dialect=dialect,
5584        copy=copy,
5585        **opts,
5586    )
5587
5588
5589def and_(
5590    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5591) -> Condition:
5592    """
5593    Combine multiple conditions with an AND logical operator.
5594
5595    Example:
5596        >>> and_("x=1", and_("y=1", "z=1")).sql()
5597        'x = 1 AND (y = 1 AND z = 1)'
5598
5599    Args:
5600        *expressions: the SQL code strings to parse.
5601            If an Expression instance is passed, this is used as-is.
5602        dialect: the dialect used to parse the input expression.
5603        copy: whether or not to copy `expressions` (only applies to Expressions).
5604        **opts: other options to use to parse the input expressions.
5605
5606    Returns:
5607        And: the new condition
5608    """
5609    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5610
5611
5612def or_(
5613    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5614) -> Condition:
5615    """
5616    Combine multiple conditions with an OR logical operator.
5617
5618    Example:
5619        >>> or_("x=1", or_("y=1", "z=1")).sql()
5620        'x = 1 OR (y = 1 OR z = 1)'
5621
5622    Args:
5623        *expressions: the SQL code strings to parse.
5624            If an Expression instance is passed, this is used as-is.
5625        dialect: the dialect used to parse the input expression.
5626        copy: whether or not to copy `expressions` (only applies to Expressions).
5627        **opts: other options to use to parse the input expressions.
5628
5629    Returns:
5630        Or: the new condition
5631    """
5632    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5633
5634
5635def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5636    """
5637    Wrap a condition with a NOT operator.
5638
5639    Example:
5640        >>> not_("this_suit='black'").sql()
5641        "NOT this_suit = 'black'"
5642
5643    Args:
5644        expression: the SQL code string to parse.
5645            If an Expression instance is passed, this is used as-is.
5646        dialect: the dialect used to parse the input expression.
5647        copy: whether to copy the expression or not.
5648        **opts: other options to use to parse the input expressions.
5649
5650    Returns:
5651        The new condition.
5652    """
5653    this = condition(
5654        expression,
5655        dialect=dialect,
5656        copy=copy,
5657        **opts,
5658    )
5659    return Not(this=_wrap(this, Connector))
5660
5661
5662def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5663    """
5664    Wrap an expression in parentheses.
5665
5666    Example:
5667        >>> paren("5 + 3").sql()
5668        '(5 + 3)'
5669
5670    Args:
5671        expression: the SQL code string to parse.
5672            If an Expression instance is passed, this is used as-is.
5673        copy: whether to copy the expression or not.
5674
5675    Returns:
5676        The wrapped expression.
5677    """
5678    return Paren(this=maybe_parse(expression, copy=copy))
5679
5680
5681SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5682
5683
5684@t.overload
5685def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5686    ...
5687
5688
5689@t.overload
5690def to_identifier(
5691    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5692) -> Identifier:
5693    ...
5694
5695
5696def to_identifier(name, quoted=None, copy=True):
5697    """Builds an identifier.
5698
5699    Args:
5700        name: The name to turn into an identifier.
5701        quoted: Whether or not force quote the identifier.
5702        copy: Whether or not to copy a passed in Identefier node.
5703
5704    Returns:
5705        The identifier ast node.
5706    """
5707
5708    if name is None:
5709        return None
5710
5711    if isinstance(name, Identifier):
5712        identifier = maybe_copy(name, copy)
5713    elif isinstance(name, str):
5714        identifier = Identifier(
5715            this=name,
5716            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5717        )
5718    else:
5719        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5720    return identifier
5721
5722
5723INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5724
5725
5726def to_interval(interval: str | Literal) -> Interval:
5727    """Builds an interval expression from a string like '1 day' or '5 months'."""
5728    if isinstance(interval, Literal):
5729        if not interval.is_string:
5730            raise ValueError("Invalid interval string.")
5731
5732        interval = interval.this
5733
5734    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5735
5736    if not interval_parts:
5737        raise ValueError("Invalid interval string.")
5738
5739    return Interval(
5740        this=Literal.string(interval_parts.group(1)),
5741        unit=Var(this=interval_parts.group(2)),
5742    )
5743
5744
5745@t.overload
5746def to_table(sql_path: str | Table, **kwargs) -> Table:
5747    ...
5748
5749
5750@t.overload
5751def to_table(sql_path: None, **kwargs) -> None:
5752    ...
5753
5754
5755def to_table(
5756    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
5757) -> t.Optional[Table]:
5758    """
5759    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
5760    If a table is passed in then that table is returned.
5761
5762    Args:
5763        sql_path: a `[catalog].[schema].[table]` string.
5764        dialect: the source dialect according to which the table name will be parsed.
5765        kwargs: the kwargs to instantiate the resulting `Table` expression with.
5766
5767    Returns:
5768        A table expression.
5769    """
5770    if sql_path is None or isinstance(sql_path, Table):
5771        return sql_path
5772    if not isinstance(sql_path, str):
5773        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
5774
5775    table = maybe_parse(sql_path, into=Table, dialect=dialect)
5776    if table:
5777        for k, v in kwargs.items():
5778            table.set(k, v)
5779
5780    return table
5781
5782
5783def to_column(sql_path: str | Column, **kwargs) -> Column:
5784    """
5785    Create a column from a `[table].[column]` sql path. Schema is optional.
5786
5787    If a column is passed in then that column is returned.
5788
5789    Args:
5790        sql_path: `[table].[column]` string
5791    Returns:
5792        Table: A column expression
5793    """
5794    if sql_path is None or isinstance(sql_path, Column):
5795        return sql_path
5796    if not isinstance(sql_path, str):
5797        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
5798    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
5799
5800
5801def alias_(
5802    expression: ExpOrStr,
5803    alias: str | Identifier,
5804    table: bool | t.Sequence[str | Identifier] = False,
5805    quoted: t.Optional[bool] = None,
5806    dialect: DialectType = None,
5807    copy: bool = True,
5808    **opts,
5809):
5810    """Create an Alias expression.
5811
5812    Example:
5813        >>> alias_('foo', 'bar').sql()
5814        'foo AS bar'
5815
5816        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
5817        '(SELECT 1, 2) AS bar(a, b)'
5818
5819    Args:
5820        expression: the SQL code strings to parse.
5821            If an Expression instance is passed, this is used as-is.
5822        alias: the alias name to use. If the name has
5823            special characters it is quoted.
5824        table: Whether or not to create a table alias, can also be a list of columns.
5825        quoted: whether or not to quote the alias
5826        dialect: the dialect used to parse the input expression.
5827        copy: Whether or not to copy the expression.
5828        **opts: other options to use to parse the input expressions.
5829
5830    Returns:
5831        Alias: the aliased expression
5832    """
5833    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5834    alias = to_identifier(alias, quoted=quoted)
5835
5836    if table:
5837        table_alias = TableAlias(this=alias)
5838        exp.set("alias", table_alias)
5839
5840        if not isinstance(table, bool):
5841            for column in table:
5842                table_alias.append("columns", to_identifier(column, quoted=quoted))
5843
5844        return exp
5845
5846    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
5847    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
5848    # for the complete Window expression.
5849    #
5850    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
5851
5852    if "alias" in exp.arg_types and not isinstance(exp, Window):
5853        exp.set("alias", alias)
5854        return exp
5855    return Alias(this=exp, alias=alias)
5856
5857
5858def subquery(
5859    expression: ExpOrStr,
5860    alias: t.Optional[Identifier | str] = None,
5861    dialect: DialectType = None,
5862    **opts,
5863) -> Select:
5864    """
5865    Build a subquery expression.
5866
5867    Example:
5868        >>> subquery('select x from tbl', 'bar').select('x').sql()
5869        'SELECT x FROM (SELECT x FROM tbl) AS bar'
5870
5871    Args:
5872        expression: the SQL code strings to parse.
5873            If an Expression instance is passed, this is used as-is.
5874        alias: the alias name to use.
5875        dialect: the dialect used to parse the input expression.
5876        **opts: other options to use to parse the input expressions.
5877
5878    Returns:
5879        A new Select instance with the subquery expression included.
5880    """
5881
5882    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
5883    return Select().from_(expression, dialect=dialect, **opts)
5884
5885
5886def column(
5887    col: str | Identifier,
5888    table: t.Optional[str | Identifier] = None,
5889    db: t.Optional[str | Identifier] = None,
5890    catalog: t.Optional[str | Identifier] = None,
5891    quoted: t.Optional[bool] = None,
5892) -> Column:
5893    """
5894    Build a Column.
5895
5896    Args:
5897        col: Column name.
5898        table: Table name.
5899        db: Database name.
5900        catalog: Catalog name.
5901        quoted: Whether to force quotes on the column's identifiers.
5902
5903    Returns:
5904        The new Column instance.
5905    """
5906    return Column(
5907        this=to_identifier(col, quoted=quoted),
5908        table=to_identifier(table, quoted=quoted),
5909        db=to_identifier(db, quoted=quoted),
5910        catalog=to_identifier(catalog, quoted=quoted),
5911    )
5912
5913
5914def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
5915    """Cast an expression to a data type.
5916
5917    Example:
5918        >>> cast('x + 1', 'int').sql()
5919        'CAST(x + 1 AS INT)'
5920
5921    Args:
5922        expression: The expression to cast.
5923        to: The datatype to cast to.
5924
5925    Returns:
5926        The new Cast instance.
5927    """
5928    expression = maybe_parse(expression, **opts)
5929    return Cast(this=expression, to=DataType.build(to, **opts))
5930
5931
5932def table_(
5933    table: Identifier | str,
5934    db: t.Optional[Identifier | str] = None,
5935    catalog: t.Optional[Identifier | str] = None,
5936    quoted: t.Optional[bool] = None,
5937    alias: t.Optional[Identifier | str] = None,
5938) -> Table:
5939    """Build a Table.
5940
5941    Args:
5942        table: Table name.
5943        db: Database name.
5944        catalog: Catalog name.
5945        quote: Whether to force quotes on the table's identifiers.
5946        alias: Table's alias.
5947
5948    Returns:
5949        The new Table instance.
5950    """
5951    return Table(
5952        this=to_identifier(table, quoted=quoted) if table else None,
5953        db=to_identifier(db, quoted=quoted) if db else None,
5954        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
5955        alias=TableAlias(this=to_identifier(alias)) if alias else None,
5956    )
5957
5958
5959def values(
5960    values: t.Iterable[t.Tuple[t.Any, ...]],
5961    alias: t.Optional[str] = None,
5962    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
5963) -> Values:
5964    """Build VALUES statement.
5965
5966    Example:
5967        >>> values([(1, '2')]).sql()
5968        "VALUES (1, '2')"
5969
5970    Args:
5971        values: values statements that will be converted to SQL
5972        alias: optional alias
5973        columns: Optional list of ordered column names or ordered dictionary of column names to types.
5974         If either are provided then an alias is also required.
5975
5976    Returns:
5977        Values: the Values expression object
5978    """
5979    if columns and not alias:
5980        raise ValueError("Alias is required when providing columns")
5981
5982    return Values(
5983        expressions=[convert(tup) for tup in values],
5984        alias=(
5985            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
5986            if columns
5987            else (TableAlias(this=to_identifier(alias)) if alias else None)
5988        ),
5989    )
5990
5991
5992def var(name: t.Optional[ExpOrStr]) -> Var:
5993    """Build a SQL variable.
5994
5995    Example:
5996        >>> repr(var('x'))
5997        '(VAR this: x)'
5998
5999        >>> repr(var(column('x', table='y')))
6000        '(VAR this: x)'
6001
6002    Args:
6003        name: The name of the var or an expression who's name will become the var.
6004
6005    Returns:
6006        The new variable node.
6007    """
6008    if not name:
6009        raise ValueError("Cannot convert empty name into var.")
6010
6011    if isinstance(name, Expression):
6012        name = name.name
6013    return Var(this=name)
6014
6015
6016def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6017    """Build ALTER TABLE... RENAME... expression
6018
6019    Args:
6020        old_name: The old name of the table
6021        new_name: The new name of the table
6022
6023    Returns:
6024        Alter table expression
6025    """
6026    old_table = to_table(old_name)
6027    new_table = to_table(new_name)
6028    return AlterTable(
6029        this=old_table,
6030        actions=[
6031            RenameTable(this=new_table),
6032        ],
6033    )
6034
6035
6036def convert(value: t.Any, copy: bool = False) -> Expression:
6037    """Convert a python value into an expression object.
6038
6039    Raises an error if a conversion is not possible.
6040
6041    Args:
6042        value: A python object.
6043        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6044
6045    Returns:
6046        Expression: the equivalent expression object.
6047    """
6048    if isinstance(value, Expression):
6049        return maybe_copy(value, copy)
6050    if isinstance(value, str):
6051        return Literal.string(value)
6052    if isinstance(value, bool):
6053        return Boolean(this=value)
6054    if value is None or (isinstance(value, float) and math.isnan(value)):
6055        return NULL
6056    if isinstance(value, numbers.Number):
6057        return Literal.number(value)
6058    if isinstance(value, datetime.datetime):
6059        datetime_literal = Literal.string(
6060            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6061        )
6062        return TimeStrToTime(this=datetime_literal)
6063    if isinstance(value, datetime.date):
6064        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6065        return DateStrToDate(this=date_literal)
6066    if isinstance(value, tuple):
6067        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6068    if isinstance(value, list):
6069        return Array(expressions=[convert(v, copy=copy) for v in value])
6070    if isinstance(value, dict):
6071        return Map(
6072            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6073            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6074        )
6075    raise ValueError(f"Cannot convert {value}")
6076
6077
6078def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6079    """
6080    Replace children of an expression with the result of a lambda fun(child) -> exp.
6081    """
6082    for k, v in expression.args.items():
6083        is_list_arg = type(v) is list
6084
6085        child_nodes = v if is_list_arg else [v]
6086        new_child_nodes = []
6087
6088        for cn in child_nodes:
6089            if isinstance(cn, Expression):
6090                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6091                    new_child_nodes.append(child_node)
6092                    child_node.parent = expression
6093                    child_node.arg_key = k
6094            else:
6095                new_child_nodes.append(cn)
6096
6097        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6098
6099
6100def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6101    """
6102    Return all table names referenced through columns in an expression.
6103
6104    Example:
6105        >>> import sqlglot
6106        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6107        ['a', 'c']
6108
6109    Args:
6110        expression: expression to find table names.
6111        exclude: a table name to exclude
6112
6113    Returns:
6114        A list of unique names.
6115    """
6116    return {
6117        table
6118        for table in (column.table for column in expression.find_all(Column))
6119        if table and table != exclude
6120    }
6121
6122
6123def table_name(table: Table | str, dialect: DialectType = None) -> str:
6124    """Get the full name of a table as a string.
6125
6126    Args:
6127        table: Table expression node or string.
6128        dialect: The dialect to generate the table name for.
6129
6130    Examples:
6131        >>> from sqlglot import exp, parse_one
6132        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6133        'a.b.c'
6134
6135    Returns:
6136        The table name.
6137    """
6138
6139    table = maybe_parse(table, into=Table)
6140
6141    if not table:
6142        raise ValueError(f"Cannot parse {table}")
6143
6144    return ".".join(
6145        part.sql(dialect=dialect, identify=True)
6146        if not SAFE_IDENTIFIER_RE.match(part.name)
6147        else part.name
6148        for part in table.parts
6149    )
6150
6151
6152def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6153    """Replace all tables in expression according to the mapping.
6154
6155    Args:
6156        expression: expression node to be transformed and replaced.
6157        mapping: mapping of table names.
6158        copy: whether or not to copy the expression.
6159
6160    Examples:
6161        >>> from sqlglot import exp, parse_one
6162        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6163        'SELECT * FROM c'
6164
6165    Returns:
6166        The mapped expression.
6167    """
6168
6169    def _replace_tables(node: Expression) -> Expression:
6170        if isinstance(node, Table):
6171            new_name = mapping.get(table_name(node))
6172            if new_name:
6173                return to_table(
6174                    new_name,
6175                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6176                )
6177        return node
6178
6179    return expression.transform(_replace_tables, copy=copy)
6180
6181
6182def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6183    """Replace placeholders in an expression.
6184
6185    Args:
6186        expression: expression node to be transformed and replaced.
6187        args: positional names that will substitute unnamed placeholders in the given order.
6188        kwargs: keyword arguments that will substitute named placeholders.
6189
6190    Examples:
6191        >>> from sqlglot import exp, parse_one
6192        >>> replace_placeholders(
6193        ...     parse_one("select * from :tbl where ? = ?"),
6194        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6195        ... ).sql()
6196        "SELECT * FROM foo WHERE str_col = 'b'"
6197
6198    Returns:
6199        The mapped expression.
6200    """
6201
6202    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6203        if isinstance(node, Placeholder):
6204            if node.name:
6205                new_name = kwargs.get(node.name)
6206                if new_name:
6207                    return convert(new_name)
6208            else:
6209                try:
6210                    return convert(next(args))
6211                except StopIteration:
6212                    pass
6213        return node
6214
6215    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6216
6217
6218def expand(
6219    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6220) -> Expression:
6221    """Transforms an expression by expanding all referenced sources into subqueries.
6222
6223    Examples:
6224        >>> from sqlglot import parse_one
6225        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6226        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6227
6228        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6229        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6230
6231    Args:
6232        expression: The expression to expand.
6233        sources: A dictionary of name to Subqueryables.
6234        copy: Whether or not to copy the expression during transformation. Defaults to True.
6235
6236    Returns:
6237        The transformed expression.
6238    """
6239
6240    def _expand(node: Expression):
6241        if isinstance(node, Table):
6242            name = table_name(node)
6243            source = sources.get(name)
6244            if source:
6245                subquery = source.subquery(node.alias or name)
6246                subquery.comments = [f"source: {name}"]
6247                return subquery.transform(_expand, copy=False)
6248        return node
6249
6250    return expression.transform(_expand, copy=copy)
6251
6252
6253def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6254    """
6255    Returns a Func expression.
6256
6257    Examples:
6258        >>> func("abs", 5).sql()
6259        'ABS(5)'
6260
6261        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6262        'CAST(5 AS DOUBLE)'
6263
6264    Args:
6265        name: the name of the function to build.
6266        args: the args used to instantiate the function of interest.
6267        dialect: the source dialect.
6268        kwargs: the kwargs used to instantiate the function of interest.
6269
6270    Note:
6271        The arguments `args` and `kwargs` are mutually exclusive.
6272
6273    Returns:
6274        An instance of the function of interest, or an anonymous function, if `name` doesn't
6275        correspond to an existing `sqlglot.expressions.Func` class.
6276    """
6277    if args and kwargs:
6278        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6279
6280    from sqlglot.dialects.dialect import Dialect
6281
6282    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6283    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6284
6285    parser = Dialect.get_or_raise(dialect)().parser()
6286    from_args_list = parser.FUNCTIONS.get(name.upper())
6287
6288    if from_args_list:
6289        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6290    else:
6291        kwargs = kwargs or {"expressions": converted}
6292        function = Anonymous(this=name, **kwargs)
6293
6294    for error_message in function.error_messages(converted):
6295        raise ValueError(error_message)
6296
6297    return function
6298
6299
6300def true() -> Boolean:
6301    """
6302    Returns a true Boolean expression.
6303    """
6304    return Boolean(this=True)
6305
6306
6307def false() -> Boolean:
6308    """
6309    Returns a false Boolean expression.
6310    """
6311    return Boolean(this=False)
6312
6313
6314def null() -> Null:
6315    """
6316    Returns a Null expression.
6317    """
6318    return Null()
6319
6320
6321# TODO: deprecate this
6322TRUE = Boolean(this=True)
6323FALSE = Boolean(this=False)
6324NULL = Null()